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!

Design question from data mining newbie

Hi,

We currently have about 900 stored procedures which have logic to group healthcare claims into different 'Edit Groups' depending on the logic within each 'Edit' stored procedure.

Examples of the logic for the Edit stored procedures would be something like:

Edit1: Find all claims from same patient and same provider (matching on SubsriberID and ProviderID) which has a procedure code in (P1, P2, P3....P345) and a diagnosis code in (D1, D2,...D123) and does NOT have a modifer code in (M1, M2, M3)

Edit2: Find all claims from same patient and same provider (matching on SubsriberID and ProviderID) which has a procedure code in (P7, P8, P9....Pxxx) and a diagnosis code in (D1, D2,...Dyyy) and has a modifer code in (M3, M4, M7), which are dated within 120 days of each other.

Do you think one of the SQL Server 2005 Data mining algorithms (Clustering or Classification or Association Rules) could play some part in this? Most of the 900 stored procs can be grouped based on logic, I mean the logic is similar for each group and only the parameters (in brackets above) vary for each stored proc within the same group.

We're totally new to data mining, although we do have some moderately complex cubes running. Which algorithm (if any) would be the most appropriate for our needs?

Thanks for any help,

JGP

Hi,

From you examples, you data is of multimensional nature. It is definitely helpful to use Microsoft Analysis Services to create OLAP cube(s) to efficiently support browse and explore your edit groups. If you are only interested in querying your edit groups, you don't need data mining.

Data mining is good at generalizing knowledge(such as patterns) from data. It can then use learned knowldege to analyze your (new) data. For example, if you have a given set of claims described by the following table:

ClaimID Income HaveInurance City Fraud

1 x0,000 Yes FairyLand No

2 y0,000 No FraudLand Yes

.......

Suppose you can collect the above data from your claim database. Now let's say that you are interested in predict whether or not a new claim is fraud. You can train a model with one of Microsoft data mining algorithms (such as Microsoft Decision Trees). After training, you can use your model to predict new claims like this:

ClaimID Income HaveInurance City

10001 a0,000 Yes FairyLand

1002 b0,000 No FraudLand

.......

Depends on the query you use, you can get some result like this:

ClaimID Income HaveInurance City Predicted Result of Fraud

10001 a0,000 Yes FairyLand No

1002 b0,000 No FraudLand Yes

.......

In general, data mining can play a role when you need to learn patterns from your data, and then apply the patterns to analyze (new) data.

|||

Thanks for the example.

Are you saying that Data mining is relevant only in predicting stuff and not in finding relationships based on pre-defined rules, like what I initially explained?

I did see come cases where classification was done, where based on a bunch of parameters, historic claims can be classified into Edit1(cluster1) , Edit2(cluster2) etc.

Isn't this possible?

Thanks,

JGP

|||

My post is just using predicting as an example. Data mining is relavant to predicting as well as finding relation among data. For example, you can use clustering algorithm to cluster you data, and check whether there is a natural mapping between your edit groups and the cluster1 you found (as you mentioned above). This process can help you understand you data better. For example, if an Edit group can be naturally mapped to some cluster, this Edit group can be considered as well defined, since it really maps to some existing grouping (or cluster) of your data. On the other hand, you might consider merge a few groups if they belong to the same cluster, etc.

On the other hand, data mining works on the basis of probability. In other words, it can not be 100% correct most of the times. Say, for a problem with a set of rules, you can already classify each case (such as each claim) into each target group 100% correct. You don't want to use data mining, because you can not do any better than 100% correct, and data mining does not come free. But, if you need to find something unknown about your data, such as a claim is/isn't fraud or if it belongs to some unknown group, you should resort to data mining.

Good luck,

|||

Thanks, I think I'm getting the picture now.

Just to get some hands on, would you happen to know of any good tutorials\books for clustering\classification that is available for newbies?

Preferably one that works with 'well-defined' groups....

|||

A good book is Data Mining Techniques by Berry and Linhoff.

I think in your situation you can use data mining for data discovery to learn quite a bit about your data sets. It sounds like each of your "edits" are a fairly complicated set of rules and it may be difficult to determine which rules end up being more important, or which "edits" are related (if I am correct, a single record could have multiple "edits", no?)

With this is mind, you could "reverse engineer" the edits with a simple classification model -e.g. trees - to predict what factors are the "most important" in determining an edit. If all of your edits are "and" conditions, this won't do much, but if you have any with "or" conditions, you may find that a majority of records recieve an edit for only a few of the possible conditions. Using this to classify old records, as Yimin notes, is not going to be 100% accurate though, since you already have an encoding of the 100% accurate rules.

If you created a table that had for each record all of the record data and all of the possible "edits" for that record (you would likely need a nested table) you could predict "edits" based on the record data plus other "edits". This would end up in a relationship diagram describing how record data and edits are related. If you made such a model including only the edits, you would see how they are interrelated independent of record data. You could perform a similar operation using Clustering, to see if groups of edits cluster together.

In the end you could end up with a greater understanding of your data - potentially removing redundant code or streamlining in other ways. The good thing about data mining, it that it's painless - it's kind of fun to play with and you can get some good insights, but it doesn't cause any harm in the meantime....

Enjoy, and feel free to post any follow up questions.

-Jamie