Determine closedate depending on a metatable and this closedate will be used in a query

Hi, This is a diffcult issue to explain. I hope to make my problem
clear to you.

SITUATION
I'm building A SLA Query for a customer. This customer has an awkward
way to determine the SLA results ;-) Depending on a category which is
stored in a headertable (Requests) a field and logic is determined how
to get a proper Close_Date. This Close_date can be the closedate of
the request. It is also possible that the close_date is a certain
detail record (Request_lines). Also It is possible that this
close_date is the ordered_date of a certain line_item.

DONE SO FAR
I have created a metatable with rules per category. With this rule i
would like to determine a close_date. This close_date will be used as
parameter in a function from which i determine the total minutes how
long this request has endured.

GOAL
I want to create something like this:
SELECT id, getdiffSLA(@.StartDate, GetCloseDate(some parameters))
FROM...

I've created the function 'GetCloseDate()' in which i want to
determine the closedate. In this function i want to determine the
rule. Execute the proper logic and get the date.

This is what i'm trying to do in a function (this is one rule of many)
:

.....
ELSE IF @.iRuleNo = 2 --SAD_A_REQUEST_LINE.CLOSE_DATE
BEGIN
.....
.....
SET @.vcSQL = N' SELECT @.max = MAX(Close_date)
FROM samis.dbo.SAD_A_REQUEST_LINES
WHERE Parent_Quote = ''' + @.vcNumberPRGN + ''' AND Part_No In ('+
@.vcIN_String + ')'

Exec sp_executesql @.vcsql, N'@.Max datetime OUTPUT', @.max OUTPUT

SET @.dCloseDate = @.max

ELSE IF...
....

THE PROBLEM
This doesn't work and yeah i know i can't use exec /sp_executesql in a
function. But if i try this in a stored procedure i can't use this in
a Query.
SELECT id, storedprocedure FROM ... Doesn't work, also.

An option is that i could create a cursor, loop every record and call
the stored procedure, get the close_date, execute my SLA calculation
function, store the result in a temptable, use this temptable in the
query <pffff>.

But the table consists of 200000 records (with a detailtable) and
performance is a issue. It's used for loading a datawarehouse and not
in a OLTP system so a bit slow performance is allowed but not to much.

SO how do i do this without using a cursor?

Greetz

Hennie"Hennie de Nooijer" <hdenooijer@.hotmail.com> wrote in message
news:191115aa.0407062303.44ff9664@.posting.google.c om...
> Hi, This is a diffcult issue to explain. I hope to make my problem
> clear to you.
> SITUATION
> I'm building A SLA Query for a customer. This customer has an awkward
> way to determine the SLA results ;-) Depending on a category which is
> stored in a headertable (Requests) a field and logic is determined how
> to get a proper Close_Date. This Close_date can be the closedate of
> the request. It is also possible that the close_date is a certain
> detail record (Request_lines). Also It is possible that this
> close_date is the ordered_date of a certain line_item.
> DONE SO FAR
> I have created a metatable with rules per category. With this rule i
> would like to determine a close_date. This close_date will be used as
> parameter in a function from which i determine the total minutes how
> long this request has endured.
> GOAL
> I want to create something like this:
> SELECT id, getdiffSLA(@.StartDate, GetCloseDate(some parameters))
> FROM...
> I've created the function 'GetCloseDate()' in which i want to
> determine the closedate. In this function i want to determine the
> rule. Execute the proper logic and get the date.
> This is what i'm trying to do in a function (this is one rule of many)
> :
> ....
> ELSE IF @.iRuleNo = 2 --SAD_A_REQUEST_LINE.CLOSE_DATE
> BEGIN
> .....
> .....
> SET @.vcSQL = N' SELECT @.max = MAX(Close_date)
> FROM samis.dbo.SAD_A_REQUEST_LINES
> WHERE Parent_Quote = ''' + @.vcNumberPRGN + ''' AND Part_No In ('+
> @.vcIN_String + ')'
> Exec sp_executesql @.vcsql, N'@.Max datetime OUTPUT', @.max OUTPUT
> SET @.dCloseDate = @.max
> ELSE IF...
> ...
> THE PROBLEM
> This doesn't work and yeah i know i can't use exec /sp_executesql in a
> function. But if i try this in a stored procedure i can't use this in
> a Query.
> SELECT id, storedprocedure FROM ... Doesn't work, also.
> An option is that i could create a cursor, loop every record and call
> the stored procedure, get the close_date, execute my SLA calculation
> function, store the result in a temptable, use this temptable in the
> query <pffff>.
> But the table consists of 200000 records (with a detailtable) and
> performance is a issue. It's used for loading a datawarehouse and not
> in a OLTP system so a bit slow performance is allowed but not to much.
> SO how do i do this without using a cursor?
> Greetz
> Hennie

I don't really follow your description completely, but it seems that one key
issue is working with delimited strings, so you may want to look at this
article to see how to avoid using dynamic SQL:

http://www.sommarskog.se/arrays-in-sql.html

That might help you to create your function, but if not then you should
consider posting some more detailed information - the CREATE TABLE
statements for the tables, INSERTs of some sample data, and what you expect
to be returned for each category. That's usually clearer than a description,
and someone may be able to give more specific help.

Simon|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

>> Depending on a category which is stored in a header table (Requests)
a field [sic] and logic is determined how
to get a proper Close_Date. This Close_date can be the closedate of the
request. It is also possible that the close_date is a certain detail
record [sic] (Request_lines). Also It is possible that this close_date
is the ordered_date of a certain line_item. <<

Rows are not records; fields are not columns; tables are not files. I
also hope that those "vc_" prefixes did not mean "VARCHAR(n)" in
violation of ISO-11179 rules. It woiuld look like you are still writing
BASIC and procedural code, not SQL. No wonder you are thinking about
dynamic SQL kludgers and cursors.

>> I have created a metatable with rules per category. <<

I am not sure what that means. A decision table in SQL, perhaps?

>> So how do I do this without using a cursor? <<

My first thought is to write something like this:

UPDATE Requests
SET close_date
= CASE category
WHEN 1 THEN <<close_date of certain detail>>
WHEN 2 THEN <<ordered_date of certain detail>>
ELSE close_date END; -- do nothing

Without better specs, this is about as far as I can get. Each category
would lead to a scalar query expression that finds the desired data
value, maybe something like this:

CASE category
...
WHEN 2
THEN (SELECT MAX(close_date)
FROM SadRequestLines AS S1
WHERE S1.parent_quote = Requests.numberprgn
AND S1.part_no
IN (SELECT part_no FROM PartsCategory_2))

The important point is to avoid all procedural code, dynamic SQL and
cursors. If you use the CASE expression, you can move all the logic
into a single, optimizable statement. My guess would be that it should
be about 10x faster than your procedural approach.

If the conditions are really tricky, then I would consider a decision
table tool to generate the WHEN predicates. LOok up Logic Gem as an
example of such a thing.

--CELKO--

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Pfff what a lot of answers and questions again. OK i'll drop my query
stuff:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[XXX_FN_GetCloseDate]') and xtype in (N'FN', N'IF',
N'TF'))
drop function [dbo].[XXX_FN_GetCloseDate]
GO

CREATE FUNCTION XXX_FN_GetCloseDate(@.vcCategory as Varchar(50),
@.vcNumberPRGN as varchar(50)) RETURNS varchar(8000)
WITH ENCRYPTION
AS
DECLARE @.vcField Varchar(50)
DECLARE @.vcPartString Varchar(8000)
DECLARE @.iRuleNoVarchar(8000)
DECLARE @.vcsqlVarchar(8000)
DECLARE @.vcTempStringVarchar(8000)
DECLARE @.vcPartNovarchar(50)
DECLARE @.vcIN_StringVarchar(8000)
DECLARE @.dCloseDateDatetime
DECLARE @.maxDatetime

-- Intitialisation
SET @.vcIN_String = ''

DECLARE FieldsCur CURSOR
FOR
SELECT MET_Field, MET_Partno, Met_RuleNo
FROM xxxxx_control.dbo.xxxxx_PWC_SLA_Metrics
WHERE MET_Code = @.vcCategory

OPEN FieldsCur
FETCH FROM FieldsCur INTO @.vcField, @.vcPartString, @.iRuleNo

IF @.@.FETCH_STATUS = 0
BEGIN
IF @.iRuleNo = 1 --SAD_A_REQUESTS.CLOSE_DATE
BEGIN
SET @.vcSQL = 'SELECT '''+ @.vcCategory + ''',''' +
@.vcNumberPRGN + ''',' + 'Close_Date FROM xxxxxx.dbo.SAD_A_REQUESTS
WHERE NumberPRGN = ' + @.vcNumberPRGN
END
ELSE IF @.iRuleNo = 2 --SAD_A_REQUEST_LINE.CLOSE_DATE
BEGIN
-- Extract the partnos from the MET_Field.
SET @.vctempString = LTRIM(RTRIM(@.vcPartString))
WHILE Len(@.vctempString) <> 0
BEGIN
SET @.vcPartString =
xxxxx_Control.dbo.xxxxx_FN_Get_FirstElement_IN_CSV (@.vctempString)
SET @.vcPartNo = LTRIM(RTRIM(@.vcPartString))
IF CHARINDEX(',', @.vctempString)<> 0
SET @.vcTempString = SubString(@.vctempString, CHARINDEX(',',
@.vctempString)+1, Len(@.vctempString) )
ELSE
SET @.vcTempString = ''
-- This string is created voor de IN in the Query.
IF Len(@.vctempString) <> 0 -- comma is needed
SET @.vcIN_String = @.vcIN_String + '''' + @.vcPartNo + '''' + ','ELSE
SET @.vcIN_String = @.vcIN_String + '''' + @.vcPartNo + ''''

END
SET @.vcSQL = 'SELECT '''+ @.vcCategory + ''',''' + @.vcNumberPRGN + ''','
+ 'MAX(Close_date) FROM xxxxx.dbo.SAD_A_REQUEST_LINES WHERE
Parent_Quote = ''' + @.vcNumberPRGN + ''' AND Part_No In ('+
@.vcIN_String + ')'

CREATE TABLE #CloseDate (CloseDate DateTime)
INSERT INTO CloseDate EXEC ('SELECT MAX(Close_date) FROM
xxxxx.dbo.SAD_A_REQUEST_LINES WHERE Parent_Quote = ''' + @.vcNumberPRGN
+ ''' AND Part_No In ('+ @.vcIN_String + ')')
SET @.dCloseDate = (SELECT CloseDate FROM #CloseDate)
DROP TABLE #CloseDate
END
ELSE IF @.iRuleNo = 3
BEGIN
-- Extract the partnos from the MET_Field.
SET @.vctempString = LTRIM(RTRIM(@.vcPartString))
WHILE Len(@.vctempString) <> 0
BEGIN
SET @.vcPartString =
xxxxx_Control.dbo.xxxxx_FN_Get_FirstElement_IN_CSV (@.vctempString)
SET @.vcPartNo = LTRIM(RTRIM(@.vcPartString))

IF CHARINDEX(',', @.vctempString)<> 0
SET @.vcTempString = SubString(@.vctempString, CHARINDEX(',',
@.vctempString)+1, Len(@.vctempString) )
ELSE
SET @.vcTempString = ''

-- This string is created voor de IN in the Query.
IF Len(@.vctempString) <> 0 -- comma is needed
SET @.vcIN_String = @.vcIN_String + '''' + @.vcPartNo + '''' + ','
ELSE
SET @.vcIN_String = @.vcIN_String + '''' + @.vcPartNo + ''''

--Print '@.vcIN_String : ' + @.vcIN_String

END
--CREATE TABLE #OrderedDate (OrderedDate DateTime)
--INSERT INTO #OrderedDate
SET @.vcSQL = 'SELECT '+ @.vcCategory + ',' + @.vcNumberPRGN + ',' +
'MAX(Ordered_date) FROM xxxxx.dbo.SAD_A_REQUEST_LINES WHERE
Parent_Quote = ''' + @.vcNumberPRGN + ''' AND Part_No In ('+
@.vcIN_String + ')'
-- SET @.vcSQL = 'SELECT @.max = MAX(Ordered_date) FROM
xxxxx.dbo.SAD_A_REQUEST_LINES WHERE Parent_Quote = ''' + @.vcNumberPRGN
+ ''' AND Part_No In ('+ @.vcIN_String + ')'

Exec sp_executesql @.vcsql, N'@.Max datetime OUTPUT', @.max OUTPUT
SET @.dCloseDate = @.max
SET @.dCloseDate = (SELECT OrderedDate FROM #OrderedDate)
DROP TABLE #OrderedDate
END
--ELSE IF @.iRuleNo = 4
--BEGIN

--END

--Print CAST(@.dCloseDate as varchar)

END

CLOSE FieldsCur
DEALLOCATE FieldsCur

--RETURN @.dCloseDate
RETURN @.vcSQL
END

----------------

I've edited it quite a lot so i hope it's readable...

That i was creating it as a procedural program has been an idea of
myself too. So i tried to get the logic in a query: So tried this:

SELECT
A.NUMBERPRGN,
A.SubCategory,
C.MET_Field,
C.MET_Partno,
C.Met_RuleNo,
CASE
WHEN Met_RuleNo = 1 THEN
(SELECT cast(Close_Date as varchar) FROM xxxxx.dbo.SAD_A_REQUESTS WHERE
NumberPRGN = A.NumberPRGN)

WHEN Met_RuleNo = 2 THEN
(SELECT cast(MAX(Close_date) as varchar)
FROM xxxxx.dbo.SAD_A_REQUEST_LINES B WHERE B.Parent_Quote = A.NumberPRGN
AND B.Part_No In (SELECT PartNoFROM
xxxxxx_Control.dbo.xxxxx_FN_Convert_From_CSVTOTabl e(C.MET_Partno)))

WHEN Met_RuleNo = 3 THEN '3' ELSE '999999999999' END
FROM xxxxxx.dbo.SAD_A_Requests A
LEFT OUTER JOIN xxxxxx._Control.dbo.xxxxxx_PWC_SLA_Metrics C ON
A.SubCategory = C.MET_Code

I'm almost there where i want because When i replace C.MET_Partno in the
inline function (returns a table) with a string like
'COM_06,ATC_04,ANC_03,ALC_03,AAC_04,ASC_02,APC_05' it's working but i I
got the following error:
Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near '.'.

Research teached me that Inline functions doesn't allow fields as
parameter only variables or constants. So now i'm right at the start and
running out of ideas. This problem is driving me nuts (Aargh)..

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hennie de Nooijer (hdenooijer@.hotmail.com) writes:
> I'm building A SLA Query for a customer. This customer has an awkward
> way to determine the SLA results ;-)

I know what a TLA is, but what is an SLA?

> An option is that i could create a cursor, loop every record and call
> the stored procedure, get the close_date, execute my SLA calculation
> function, store the result in a temptable, use this temptable in the
> query <pffff>.
> But the table consists of 200000 records (with a detailtable) and
> performance is a issue. It's used for loading a datawarehouse and not
> in a OLTP system so a bit slow performance is allowed but not to much.

Then you consider this: if you say:

SELECT dbo.my_udf(col) FROM tbl

then your SELECT statement becomes a cursor behind the scenes. SQL Server
does not have any method to call you function for all rows at once. It
may be faster than a real cursor, but in comparison with UDF-less SELECT
statement the difference may be stunning.

For your actual problem there is too little information to suggest
something, and in any case, it may too complex to address completely in
a newsgroup posting.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||This is the second time i enter a complete message and when i push
submit it is all gone. I hate this. Great. I forgot to click on the
radiobutton??!!! I will notice soon. Grrrrr. So my answer is shorter now
than i want.

So in short. SLA is SErvice line agreement and is an agreement between a
customer and a service deliverer.

I solved this issue by creating a temporary table and joining with this
table. Perhaps i didn't explain it to well or the problem was to
complicated. Thanx anyway...

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!