15,312,405 members
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 20:42pm
v2
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#.

## Solution 1

`Select a.*, (Select SUM(Paid-Received) From #temp b where b.Code<=a.code) As Invested From #temp a Order By a.Code`

## Solution 2

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

## Solution 3

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