Run Total and movement amounts calculations

[eo]

Hi,

I use SQL Server 2005. I have asked a related question before, but not quite the same, so thought it best to start a new thread.

I have some values, and I want to set an upper limit on their cumilative values. This is easy to show on a small dataset.

> The first col is ClaimNumber, and this should reset all calculations (i.o.w. this logic should apply for each ClaimNumber)
> The second column is a date
> The third Column is the original ClaimValue
> The fouth column is a required calculated value which shows a running total based on the second column (ClaimValue_RT)
> The fifth column should set the ClaimValue_RT to a maximum (i.o.w. it should replace the RT value with an absolute maximim, but if the RT causes a dip below the maximum, then show that dip) = TopValue
> The sixth column is the one column actually required and shows the TopValue_Movement from one record to a next

Assuming a TopValue requirement of 150:

ClmNo Date  ClmVal  ClmVal_RT TopVal  TopVal_Mvmt 
001   01/05   100    100      100      100
001   02/05   200    300      150       50
001   03/05   150    450      150        0
001   04/05   500    950      150        0
001   05/05 -1000    -50      -50     -200

ClmNo, Date and ClmVal are known columns, but ClmVal_RT, TopVal and TopVal_Mvmt must be calculated.

Is this possible?

EO
Hertfordshire, England

 

[RyanEK]

Hi,

Could you explain again how you calculate the TopVal field? It didn't make sense to me. To calculate the running total though you could do the following...

declare @test table (clmno int, date datetime, clmval int)
insert into @test values (1, '2008-05-01', 100)
insert into @test values (1, '2008-05-02', 200)
insert into @test values (1, '2008-05-03', 150)
insert into @test values (1, '2008-05-04', 500)
insert into @test values (1, '2008-05-05', -1000)

select t1.*,
( select sum(clmval) 
 from @test t2 
 where t1.clmno = t2.clmno and t2.date <= t1.date
) as ClmVal_RT
from @test t1

Ryan

 

[eo]

The top value limits the ClmVal_RT to 150. Its simply a case statement

case when ClmVal_RT < 150 then ClmVal_RT else 150 end as TopVal

Remember that ClmNo, Date & ClmVal are given, I then calculate the ClmVal_RT using a similar syntax to the one you provided, then apply the case statement to calculate the TopVal, but I now need to calculate the TopVal_Mvmt (which is a movement calculation between a TopVal and its preceding value)

EO
Hertfordshire, England