How to find out cumulative Amount in a table

Question

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 9-Feb-14 21:51pm

kirthiga S

Updated 9-Feb-14 22:02pm

OriginalGriff

v3

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Kornfeld Eliyahu Peter · Answer 1 · 2014-02-10T00:17:00

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