Using a cursor and sum a value up to a limit 1

[eo]

Hi,

SQL server 2005

I have a table with a value, a date and a claim number (amonst many other fields). I want to create a new calculated value (field) that shows the original value, but only until a top limit has been reached and for this to reset with every claim number. This could easily be done by grouping by the claim number and the date and using a case statement to do the calculatioon, but I cannot re-granularise my records. I was therefore hoping to use some cursor function to do this.

Assuming the upper value is 520, I will explain by example (the example shows two claim numbers 23 and 24 both over the 520 limit, and claim number 25 well within the 520 limit):

ClaimNo   Date        Value New520TopLimitValue
23        01/01/2006  100   100
23        01/06/2007  100   100   
23        25/07/2007  100   100
23        01/01/2008  100   100
23        13/01/2008  100   100
23        04/02/2008  100    20
23        13/11/2008  100     0
24        14/08/2004  300   300
24        18/08/2004  200   200
24        16/07/2008  348    20
24        18/11/2008  600     0
24        19/11/2008  700     0
25        01/02/2008   16    16
25        02/02/2008  400   400

Is this possible?

EO
Hertfordshire, England

 

[gmmastros]

Is this possible?

You betcha!

Select A.ClaimNo, 
       A.Date, 
       A.Value,
       Case When Sum(B.Value) > 520 
            Then Case When 520 - (Sum(B.Value) - A.Value) < 0 
                  Then 0 
                  Else 520 - (Sum(B.Value) - A.Value) 
                  End 
             Else A.Value 
             End As New520TopLimitValue
From   YourTableName As A
       Inner Join YourTableName As B
          On A.ClaimNo = B.ClaimNo
          And A.Date >= B.Date
Group By A.ClaimNo, A.Date, A.Value

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[eo]

This seems like a usefull option, but we have very large tables and joining them to themselves (I think) will create problems elsewhere, is it not easier to use some sort of cursor logic?

EO
Hertfordshire, England

 

[gmmastros]

It's possible that a cursor may perform better than a self-join in this situation. It depends on a lot of things. While your table may be large, if there is a relatively small number of rows/claim, then my gut tells me that the self-join method will be faster.

Honestly... I don't do cursors, so I won't be able to help you there. However, I do encourage you to pursue the cursor option. When you have a working cursor solution, please compare the performance with the query I presented above. I'm very curious to know which is better.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[eo]

Noted, I have decided to focus in stead on your proposed solution as I have read "cursors are valuable but dangerous tools" and the like...so now some questions re your proposal...

you propose to sum(B.Value)

does that then not mean that we have to group the B table at some point? I am trying to translate my simple example provided into your syntaxm, but not getting to the New520TopLimitValue...but stand corrected

EO
Hertfordshire, England

 

[gmmastros]

No. You don't need to group on the B table. Only if we had included that as a return column (in the select clause).

Maybe it would help if I showed you my test. I dummied up your sample data and then wrote a query that uses the sample data. Copy/paste this to a query window to see how it works. If the table structure (for the sample data) does not match your real table, then modify the code below to better demonstrate the real tables. Get it working there and then convert it (at the last minute) to use your real tables instead.

SET DATEFORMAT DMY

Declare @Temp Table(ClaimNo Int, Date DateTime, Value Int)

Insert Into @Temp Values(23,'01/01/2006',  100)
Insert Into @Temp Values(23,'01/06/2007',  100)  
Insert Into @Temp Values(23,'25/07/2007',  100)
Insert Into @Temp Values(23,'01/01/2008',  100)
Insert Into @Temp Values(23,'13/01/2008',  100)
Insert Into @Temp Values(23,'04/02/2008',  100)
Insert Into @Temp Values(23,'13/11/2008',  100)
Insert Into @Temp Values(24,'14/08/2004',  300)
Insert Into @Temp Values(24,'18/08/2004',  200)
Insert Into @Temp Values(24,'16/07/2008',  348)
Insert Into @Temp Values(24,'18/11/2008',  600)
Insert Into @Temp Values(24,'19/11/2008',  700)
Insert Into @Temp Values(25,'01/02/2008',   16)
Insert Into @Temp Values(25,'02/02/2008',  400)

Select A.ClaimNo, 
       A.Date, 
       A.Value,
       Case When Sum(B.Value) > 520 
            Then Case When 520 - (Sum(B.Value) - A.Value) < 0 
                      Then 0 
                      Else 520 - (Sum(B.Value) - A.Value) 
                      End 
            Else A.Value 
            End As New520TopLimitValue
From   @Temp As A
       Inner Join @Temp As B
         On A.ClaimNo = B.ClaimNo
         And A.Date >= B.Date
Group By A.ClaimNo, A.Date, A.Value

When you run this code, you should see that the output exactly matches your expected output.

By the way, it's probably not a 'great' idea to abandon the cursor approach. There are VERY rare occasions where a cursor outperforms set based code. A running total is one of those occasions. What you have here is a modification to that, so it is possible that a cursor solution may be faster. Like I said earlier, it may depend on the data, too.

Hope this helps.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[eo]

I tried your method, and also created a summarised version of "B" and both returned the same results

ClaimNo Date                    Value   New520TopLimitValue
23	2006-01-01 00:00:00.000	100	0
23	2007-06-01 00:00:00.000	100	0
23	2007-07-25 00:00:00.000	100	0
23	2008-01-01 00:00:00.000	100	0
23	2008-01-13 00:00:00.000	100	0
23	2008-02-04 00:00:00.000	100	0
23	2008-11-13 00:00:00.000	100	0
24	2004-08-14 00:00:00.000	300	0
24	2004-08-18 00:00:00.000	200	0
24	2008-07-16 00:00:00.000	348	0
24	2008-11-18 00:00:00.000	600	0
24	2008-11-19 00:00:00.000	700	0
25	2008-02-01 00:00:00.000	16	16
25	2008-02-02 00:00:00.000	400	400

EO
Hertfordshire, England

 

[gmmastros]

Take a look here for other ideas.

http://geekswithblogs.net/Rhames/ar...-totals-in-sql-server-2005---the-optimal.aspx

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[eo]

My apologies, this does work, except for the two entries and these are where the A.Value > 520

24    2008-11-18 00:00:00.000    600
24    2008-11-19 00:00:00.000    700

I would have thought that your syntax would treat these the same as Sum(B.Value) > 520

Any idea how to account for such anomalies

EO
Hertfordshire, England

 

[eo]

IGNORE MY PREV POST!!!!

EO
Hertfordshire, England

 

[eo]

We were well on our way to use a combination of your solution and cursors, just to discover a reality in the data which will not make this solution possible. There are many large minus values, and following this rule will cause major inaccuracies as per the business rule. The exercise was very helpful though, even if just to increase my knowledge of the subject matter!!

EO
Hertfordshire, England