Query help - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Query help

Author

Topic

jigsh
Starting Member

6 Posts

Posted - 2008-09-15 : 13:57:36

I have following data:

Date Id Amount
2/1/2008 1 -3000
2/1/2008 1 -3000
2/1/2008 1 3000
2/1/2008 1 3000
3/1/2008 2 -3500
3/1/2008 2 -3000
3/1/2008 2 3500
3/1/2008 2 500
3/1/2008 2 3000
4/1/2008 3 -3800
4/1/2008 3 -3000
4/1/2008 ? -500
4/1/2008 3 500
4/1/2008 3 3300
4/1/2008 3 3000

I would like to have Stored Procedure or View which returns the data with new colum called Balanace so the results set look like this.

Date Id Amount Balance
2/1/2008 1 -3000 3000
2/1/2008 1 -3000 6000
2/1/2008 1 3000 3000
2/1/2008 1 3000 0
3/1/2008 2 -3500 3500
3/1/2008 2 -3000 6500
3/1/2008 2 3500 3000
3/1/2008 2 500 2500
3/1/2008 2 3000 -500
4/1/2008 3 -3800 3300
4/1/2008 3 -3000 6300
4/1/2008 ? -500 6800
4/1/2008 3 500 6300
4/1/2008 3 3300 3000
4/1/2008 3 3000 0

Does any have idea to accomplish this? Can I write view

Thanks
Jigsh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 14:06:31

[code];With CTE(Seq,Date, Id, Amount) AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY Date,Id ORDER BY Date), Date, Id, Amount
FROM YourTable
)

SELECT c1.date,c1.Id,c1.Amount,COALESCE(c2.PrevBal,0)-c1.Amount AS Balance
FROM CTE c1
OUTER APPLY (SELECT TOP 1 Balance AS PrevBal
FROM CTE
WHERE Date=c1.Date
AND Id=c1.Id
AND Seq<c1.Seq
ORDER BY Seq DESC)c2[/code]

jigsh
Starting Member

6 Posts

Posted - 2008-09-15 : 14:34:25

Hi Visakh16...

thanks for your quick response but I am getting following error:

Invalid column name 'Balance'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 15:09:11

[code];With CTE(Seq,Date, Id, Amount) AS
(
SELECT ROW_NUMBER() OVER(ORDER BY Date,Id), Date, Id, Amount
FROM YourTable
)

SELECT c1.date,c1.Id,c1.Amount,ABS(COALESCE(c2.PrevAmt,0)+c1.Amount) AS Balance
FROM CTE c1
OUTER APPLY (SELECT SUM(Amount) AS PrevAmt
FROM CTE
WHERE Seq<c1.Seq)c2[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources