Accumulation of counts over time

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)

Accumulation of counts over time

Author

Topic

jj7353
Starting Member

10 Posts

Posted - 2004-12-09 : 00:42:22

Hi,

Take a look at some sample data in my table.

LOC INSTALLED_MONTH INSTALLED_CNT TOTAL_SINCE_BEGINNING
ZZZZZ 1/1/2004 2 2
ZZZZZ 2/1/2004 4 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

When I get my data it is everything presented in the table above except for the "TOTAL_SINCE_BEGINNING" column counts - I put the counts in there to deminstrate how they should be accumulating each month. I am looking for a way to derive those counts. Each month is showing what was installed and I need to also included the total "installed" up to each Month presented.

I have been working with many approaches for the last couple of days and have only discovered it to work utilizing cursors. But with over 250,000 rows in my table I estimated it would take close to 60 hours to get what I need done.

I hope this makes since.

And thanks for your help, ahead of time. Its much appreciative.

I hope this turns out where you can easily see the table - while I am writing my message and when I click the PREVIEW button to see my message it just opens up a window that says "close window".

John

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-09 : 01:54:39

you can use:

select...
from...
group by...
having...

or

select...
from... with rollup

check both on BOL

--------------------
keeping it simple...

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-12-09 : 07:32:54

Don't know how it would run on that size table, but normally a running sum can be done by


Select 
	A.LOC,
	A.INSTALLED_MONTH,
	A.INSTALLED_CNT,
	TOTAL_SINCE_BEGINNING = (select sum(INSTALLED_CNT) From yourTable Where Loc = A.LOC and INSTALLED_MONTH <= A.INSTALLED_MONTH)
From yourTable A


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

Corey

jj7353
Starting Member

10 Posts

Posted - 2004-12-09 : 09:43:28

Oh man - I wish you could of seen my face light up once I put your code in action. It worked like a champ and under 2 minutes. Thanks again Seventhnight

Sincerely,

John

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-12-09 : 09:50:39

quote:
Originally posted by jj7353

Oh man - I wish you could of seen my face light up once I put your code in action. It worked like a champ and under 2 minutes. Thanks again Seventhnight

Sincerely,

John

Very Cool!

Glad I could help

Corey

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-09 : 12:47:01

keep in mind this is usually easier and more efficiently done by the presentation layer. reporting tools and stuff like ASP and VB make it pretty easy to calc a running total as you pull the data in.

- Jeff

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-12-09 : 13:43:14

but of course

Corey

jj7353
Starting Member

10 Posts

Posted - 2004-12-13 : 11:48:06

Hi again sqlteam ...

I was so thankful to have had someone help me with my last delima,.. Seventhnight was so kind to provide me assistance. I am using the query Seventhnight provided me.

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 you notice in my example above, not all months were included. Here is another example of what my results will look like:

LOC|INSTALLED_MONTH|INSTALLED_CNT|TOTAL_SINCE_BEGINNING
ZZZZZ|1/1/2004|2|2
ZZZZZ|2/1/2004|4|6
[b]ZZZZZ|3/1/2004|0|6/[b]
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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources