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

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?

regards,

Daniel.
-- 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
BEGIN
	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 + 1
END

Open in new window

partnershipdevAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ee_rleeCommented:
hi

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.
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 1
END,
 
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 1
END;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SunBowCommented:
> calculated based on the value of the same field in the preceeding row.

Make sure you allow for calc of first row that is amenable to desired results (it has no previous row)
0
grg99Commented:
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 Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

partnershipdevAuthor Commented:
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.
0
ee_rleeCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.