Click here to Skip to main content
14,699,570 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.
Posted
Updated 26-Jun-17 21:42pm
v2
Comments
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
   
v2
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)




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