15,843,727 members
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

Posted
Updated 9-Feb-14 23:02pm
v3
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?
kirthiga S 10-Feb-14 5:26am
Its 365 only not 36525

## Solution 1

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```