Stuffing a record in there - 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 2000 Forums

Transact-SQL (2000)

Stuffing a record in there

Author

Topic

jj7353
Starting Member

10 Posts

Posted - 2004-12-14 : 09:25:08

Hi sqlteam ...

Seventhnight was so kind to provide me assistance for my last delima with deriving "rolling totals". I am using the query Seventhnight provided me.

Select
A.LOC,
A.INSTALLED_MONTH,
A.INSTALLED_CNT,
TOTAL_SINCE_BEGINNING = sum(B.INSTALLED_CNT)
From yourTable A
Cross Join yourTable B
On A.LOC = B.LOC
and A.INSTALLED_MONTH >= B.INSTALLED_MONTH
Group By A.LOC, A.INSTALLED_MONTH, A.INSTALLED_CNT

I have another delima with this same data I need solved. I need to provide every month from day one in my table. Before, if not all months were included as you can see below in my table. Here is another example of what my results need to look like:

LOC |INSTALLED_MONTH|INSTALLED_CNT|TOTAL_SINCE_BEGINNING
ZZZZZ |1/1/2004 |2 |2
ZZZZZ |2/1/2004 |4 |6
ZZZZZ|3/1/2004 |0 |6
ZZZZZ |4/1/2004 |1 |7
ZZZZZ |5/1/2004 |3 |10
AAAA |1/1/2004 |1 |1
AAAA |2/1/2004 |6 |7
AAAA |3/1/2004 |1 |8

Sorry about the look of this - I cannot seem to straigthen it out. I put some delimiters (|) in there. But notice the entry/row..."ZZZZ 3/1/2004 0 6". This is what I need my query to figure out and add it..the TOTAL_SINCE_BEGINNING counts from the previous month is carried over. I hope this makes sense.

And thank you for any help.

John

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-12-14 : 09:27:09

looks like you need to 'cross join' to a master list of possible dates/months.
Search here for 'cross join' + 'sequence number'....this is a small variation of other existing requirements.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources