Is it possible to calculate column1 based on Column 1 in a previous row
Good afternoon,
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
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?
regards,
Daniel.
-- UPDATE Survival rateDECLARE @Flag AS INTEGERDECLARE @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 = 0WHILE @Flag <=720BEGIN IF @Flag = 0 BEGIN SELECT @Survival = 1, @CC_Survival = 1 END ELSE BEGIN 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) END UPDATE #QX SET Survival = @Survival, CC_Survival = @CC_Survival WHERE Elapsed_Months = @Flag SELECT @Flag = @Flag + 1END
first get the product of all the previous (1-TotalQX). since there is no Product function, this can be done by a compination of Power, sum and log10.
then power the result to 0.0833333333333, then multiply by the 1st term.
UPDATE Survivalrate SET SURVIVAL= CASE WHEN Elapsed_Months>0 1 * POWER((SELECT POWER(10,SUM(LOG10(1-A.Total_QX))) FROM Survivalrate A WHERE A.Elapsed_Months<Survivalrate.Elapsed_Months),0.0833333333333333)ELSE 1END,CC_SURVIVAL= CASE WHEN Elapsed_Months>0 1 * POWER((SELECT POWER(10,SUM(LOG10(1-B.CC_QX))) FROM Survivalrate B WHERE B.Elapsed_Months<Survivalrate.Elapsed_Months),0.0833333333333333)ELSE 1END;
SQL is not the best language for this kind of thing.
Something more traditionally closer to the hardware, and compiled, like C, C++, Pascal, is more likely to get you a lot more speed. Like 10 to 10000 times faster.
0
The revolutionary project management tool is here! Plan visually with a single glance and make sure your projects get done.
EE_RLEE, thanks for the super quick solution however when implemented it throws a domain error in SQL ???? never come across a domain error in transact before so am not sure where this goes next ???
regards,
Daniel.
0
partnershipdevAuthor Commented:
grg_99 thanks for the response and a compiled solution would be better but is not an option in the environment i am working in ... thanks anyway.
Domain errors occur when the value provided in the mathematical function is not a valid value.
what are the values of Total_Qx & CC_Qx, because i use log10 to get the product and logs will not work on negative numbers. so Total_Qx & CC_Qx must be less than 1.
can you post some sample data?
0
partnershipdevAuthor Commented:
I have accepted this solution on the basis that it has enabled me to prove that in this particular scenario a straight loop is miles quicker than the clever maths logic required. this does indeed highlight that SQL is not the best tool for this kob and that a runtime styled solution will be required in the future, thanks for your help.
0
Featured Post
With monday.comâ€™s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.
this is how it should work.
first get the product of all the previous (1-TotalQX). since there is no Product function, this can be done by a compination of Power, sum and log10.
then power the result to 0.0833333333333, then multiply by the 1st term.
Open in new window