count previous row - 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)

count previous row

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2009-01-22 : 09:05:16

hi,
I have fields in a table showing number of starting customers and number of leaving customers.
This looks like

Year Month Starting Leaving
2006 1 10 2
2006 2 5 6

How can I also add in the calcs for Opening and Closing balances ?
so the table will look like :

Year Month Open Starting Leaving Closing
2006 1 0 10 2 8
2006 2 8 5 6 7

The table is Select year(date1) as year, month(date1) as month, count(starters), count(leavers) from custs group by year(date1), month(date1)

thank you for any help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 09:14:45

[code]
;WIth CTE(Seq,Year, Month, Starting, Leaving)
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY Year,Month),Year,Month,Starting,Leaving
FROM Table
)

SELECT c1.Year,c1.Month,COALESCE(c2.Open,0) AS Open,
c1.Starting,c1.Leaving,
COALESCE(c2.Open,0) + c1.Starting - c1.Leaving AS Leaving
Starting-Leaving AS Closing
FROM CTE c1
OUTER APPLY (SELECT TOP 1 Starting-Leaving AS Open
FROM CTE
WHERE Seq<c1.Seq
ORDER BY Seq DESC)c2
[/code]

jamie
Aged Yak Warrior

542 Posts

Posted - 2009-01-22 : 09:42:51

hi, Thank you, this looks like it is nearly working..
my opening balane seems incorrect..
should there be a comma between :
COALESCE(c2.Open,0) + c1.Starting - c1.Leaving AS Leaving
Starting-Leaving AS Closing
?

The first row is ok, but then doesn't seem to roll coreectly :

2003 8 0 2 0 2 2
2005 1 2 1 0 3 1
2005 6 1 1 0 2 1

jamie
Aged Yak Warrior

542 Posts

Posted - 2009-01-22 : 10:09:37

The opening needs to be the previous months closing.
not sure how that will work ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 10:56:05

what about this?


;WIth CTE(Seq,Year, Month, Starting, Leaving)
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY Year,Month),Year,Month,Starting,Leaving
FROM Table
)


SELECT c1.Year,c1.Month,COALESCE(c2.Open,0) AS Open,
c1.Starting,c1.Leaving,
COALESCE(c2.Open,0) + c1.Starting - c1.Leaving AS Leaving
Starting-Leaving AS Closing
FROM CTE c1
OUTER APPLY (SELECT SUM(Starting)-SUM(Leaving) AS Open
             FROM CTE
             WHERE Seq<c1.Seq)c2

if this is still incorrect, please some sample data to show what you expect

jamie
Aged Yak Warrior

542 Posts

Posted - 2009-01-22 : 11:02:08

Thank you so much ! that works perfectly.
what would we do without you !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 11:07:44

welcome