Balance Sheet - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Balance Sheet

Author

Topic

controlz
Starting Member

3 Posts

Posted - 2009-04-09 : 10:50:54

Hello gurus, I have a question about doing a balance sheet in CTE style.


CREATE TABLE TEST1
(ID INT, CDTYPE CHAR(1), INITAMT INT, AMT INT)

INSERT INTO TEST1
select 1,'C',100,10
UNION ALL
select 2,'D',100,20
UNION ALL
select 3,'D',100,30
UNION ALL
select 4,'C',100,15
UNION ALL
select 5,'D',100,20

ID   CDTYPE     INITAMT       AMT
1    C          100           10
2    D          100           20
3    D          100           30
4    C          100           15
5    D          100           20

I want to run a CTE sql to recursively calculate the cumulative amount as current amount, if CDTYPE is C then add the amount to the cumulative amount; if D then subtract from the cumulative amount:


ID   CDTYPE     INITAMT       CURRENTAMT
1    C          100           110
2    D          100           90
3    D          100           60
4    C          100           75
5    D          100           55

Thanks in advance!

controlz
Starting Member

3 Posts

Posted - 2009-04-09 : 12:04:10

How about just without Cursors :)

controlz
Starting Member

3 Posts

Posted - 2009-04-09 : 13:53:34

OK, I've figured it out myself. If you have any other idea please post here:


select a.id, a.cdtype, a.initamt, a.initamt+sum(case when cdtype = 'D' then 0-b.amt else b.amt end) as cumulativeamt
from TEST1 a left join TEST1 b
on a.id >= b.id
group by a.id, a.cdtype, a.initamt
order by a.id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-10 : 06:16:36

[code]
select a.id, a.cdtype, a.initamt, a.initamt+
(select sum(case when cdtype = 'D' then 0-b.amt else b.amt end) from TEST1 WHERE ID<=a.ID) as cumulativeamt
from TEST1 a
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources