Click here to Skip to main content
15,843,727 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear All,

I need data like this

   C1  C2  C3              C4
   --  --  --              --

R1 0   3   0               k
  
R2 1   4   k*4/365         k+(k*4/365)

R3 2   3  (k+(k*4/365))*3  [(k+(k*4/365))*3]+[k+(k*x/365)]


In values

k=4

   C1  C2  C3      C4
   --  --  --      --

R1 0   3   0       4
  
R2 1   4   0.04    4.04

R3 2   3   12.13   16.18


I need to arrive C3 & C4 column based on given formula. How to arrieve this

[edit]Code block added - OriginalGriff[/edit]
Posted
Updated 9-Feb-14 23:02pm
v3
Comments
Kornfeld Eliyahu Peter 10-Feb-14 4:09am    
It does not seem to be consistent, in R2 you have previous-row(C4) * this-row(C2) /365, but in the next row no division by 365 anymore...
Pleas clarify...
kirthiga S 10-Feb-14 4:25am    
It is consistent only. C3 should be (prev C4 * current C2) /365.

C4 should be prev C4 + current C3
Kornfeld Eliyahu Peter 10-Feb-14 4:36am    
This - (k+(k*4/365))*3 - has no the div 365...
Also it's 365 or 36525?
Try to fix your sample...
kirthiga S 10-Feb-14 5:26am    
Its 365 only not 36525

1 solution

SQL
DECLARE @C1 as int
DECLARE @C2 as int
DECLARE @C3 as float
DECLARE @C4 as float

DECLARE @LAST_C1 as int
DECLARE @LAST_C4 as float

SET @LAST_C1 = NULL

DECLARE cp_run CURSOR
    FOR SELECT C1, C2, C3, C4 FROM cp
OPEN cp_run

FETCH NEXT FROM cp_run
INTO @C1, @C2, @C3, @C4

WHILE @@FETCH_STATUS = 0
BEGIN
	IF(NOT @LAST_C1 IS NULL)
	BEGIN
		SELECT @LAST_C4 = C4 FROM CP WHERE C1 = @LAST_C1
		
		UPDATE cp SET C3 = (@LAST_C4 * @C2) / 365 WHERE C1 = @C1
		UPDATE cp SET C4 = @LAST_C4 + ((@LAST_C4 * @C2) / 365) WHERE C1 = @C1
	END
	
	SET @LAST_C1 = @C1
	
	FETCH NEXT FROM cp_run
	INTO @C1, @C2, @C3, @C4
END

CLOSE cp_run
DEALLOCATE cp_run

SELECT * FROM cp
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900