Click here to Skip to main content
15,312,257 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
|Paid Amount | Received Amount | Invested Amount
| 10000      |    --           |  10000
|   --       |   8000          |   2000
|  5000      |    --           |   7000
|   --       |   4000          |   3000

What I have tried:

I am able to do it in MS Office Excel. But want to do it in my C# code.
Updated 26-Jun-17 20:42pm
Tom Wauters 27-Jun-17 1:47am
How are the columns of paid amount and received amount orderd? Do they have some kind of datetime column? And in the title you say you want to use SQL but in 'What I have tried' it's c#.

Select a.*, (Select SUM(Paid-Received) From #temp b where b.Code<=a.code) As Invested From #temp a Order By a.Code
You have not indicated the structure of the table but I can do this in Oracle (I don't use Sql Server):

create table investments
 slno number primary key,
 type char(1),
 amount number 

I insert some records:
insert into investments values (1, 'P', 10000);
insert into investments values (2, 'R', 8000);
insert into investments values (3, 'P', 5000);
insert into investments values (4, 'R', 4000);
insert into investments values (5, 'P', 2000);
insert into investments values (6, 'P', 6000);
insert into investments values (7, 'R', 3000);

The select query is:
select slno, 
case type when 'P' then to_char(amount) else '---' end paid,
case type when 'R' then to_char(amount) else '---' end received,	
sum(amount * case type when 'P' then 1 else -1 end) over (order by slno) invested
from investments	
order by 1
You could use the lag function to get the previous row
SELECT value - lag(value) OVER (ORDER BY Id) FROM table

and then use the iif statement to see if you need to add or subtract

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

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