My question is as follows.
I have a table with 720 rows by 30 columns ... this table has been translated from excel coding and all formulas have been scrutinised and reworked to suit a set based approach to development. however I have 2 columns left (Survival and CC_Survival) that by there nature are calculated based on the value of the same field in the preceeding row.
I.e Survival starts at 1.0000000000000000 in row 1 (duration column = 0) then in row 2 (duration column =1 the survival is calculated as this
ThisSurvival = previousSurvival * (1- PreviousQX) ^ 1/12
This has presented me with alsorts of issues given the rest of the calc I can provide a single select statement using derived tables and offset inner joins (i.e. where t1.duration -1 = t2.duration)
The major issue here is in the statement Survival is not calculated at the point of update therefore there is no previous value to hook into.
I have attached the code snipped below to show my workaroud (but it adds a second to processing time (which i am not happy about given the rest of the code excutes in 16ms).
does anyone know a way to achieve this using a single update statement instead of a loop, failing that would an updated cursor be quicker?
-- UPDATE Survival rate
DECLARE @Flag AS INTEGER
DECLARE @Survival AS DECIMAL(30,25)
DECLARE @CC_Survival AS DECIMAL(30,25)
DECLARE @PREV_QX AS DECIMAL(30,25)
DECLARE @PREV_CC_QX AS DECIMAL(30,25)
DECLARE @PREV_Survival AS DECIMAL(30,25)
DECLARE @PREV_CC_Survival AS DECIMAL(30,25)
SELECT @Flag = 0
WHILE @Flag <=720
IF @Flag = 0
SELECT @Survival = 1,
@CC_Survival = 1
SELECT @Prev_QX = PREV_QX.Total_QX,
@Prev_Survival = PREV_QX.Survival,
@Prev_CC_Survival = PREV_QX.CC_Survival,
@Prev_CC_QX = PREV_QX.CC_QX
FROM #QX PREV_QX
WHERE PREV_QX.Elapsed_Months = @Flag -1
SELECT @Survival = @Prev_Survival * POWER((1-@Prev_QX),0.0833333333333333),
@CC_Survival = @Prev_CC_Survival * POWER((1-@Prev_CC_QX),0.0833333333333333)
UPDATE #QX SET Survival = @Survival, CC_Survival = @CC_Survival
WHERE Elapsed_Months = @Flag
SELECT @Flag = @Flag + 1