help with sproc ( sum by month )

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)

help with sproc ( sum by month )

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-06-16 : 16:48:11

Hi,

I'm having some difficulty figuring out how to best write this sproc. Any assistance is greatly appreciated.

I have a users table with about 100k records, each row has a join date. The dates go back approximately 3 years.

What I need to do is return the number of signups per month for everymonth from current to beginning. I also need to be able to return percentage of growth from the previous month.

Once again any help is greatly appreciated, I'm a little rough on where to start with this one.

Thanks alot guys,

Mike123

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-06-16 : 17:06:27

This is a way to get the monthly counts.


SELECT    (YEAR(JoinDate)*100) + MONTH(JoinDate), COUNT(<sign ups>)
FROM      myTable
GROUP BY  (YEAR(JoinDate)*100) + MONTH(JoinDate)

I'll have to think further on growth rates. Sounds like a presentation issue there though.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-16 : 17:12:25

SELECT COUNT(UserID), JoinDate - DAY(JoinDate) + 1
FROM Users
GROUP BY JoinDate - DAY(JoinDate) + 1

Tara

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-06-16 : 18:41:39

Thanks for the replies!

DrymChaser you hit the nail right on the head on what I am looking for, this information is very valuable to me. I've realized I need to take it a step further even.

Here's some of the data that was brought back

JoinDate / userCount

200406 4168
200405 7108
200404 7275
200403 7581
200402 6416

I'd like to add 2 columns to this

Column A - percent of total.
Column B - percent growth over last month.

This would be amazingly helpful, any assistance is greatly appreciated.

Thanks once again,

Mike123

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-06-17 : 09:09:14

Percent of Total:

add to select statement

(COUNT(<sign ups>)/(select count(p.signups) from myTable p)* 100.00) 'PercentOfTotal'

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-06-17 : 09:59:34

I need to stress again that this should be a presentation issue as performance of these queries can be poor especially on a production box with a large number of rows, but here goes.

Try this query and see if prior month and current month totals line-up. If they do then the growth can be determined via formula:
(currentmonth/(priormonth*1.00) - 1) * 100.00

SELECT	x.ReportMonth
	, x.CurrentMonthTotal
	, x.PercentOfTotal
	, y.PriorMonthTricked
	, y.PriorMonthTotal

FROM	(
	SELECT    (YEAR(JoinDate)*100) + MONTH(JoinDate) 'ReportMonth'
		, COUNT(<sign ups>) 'CurrentMonthTotal'
		, (COUNT(<sign ups>)/(select count(p.signups) from myTable p)* 100.00) 'PercentOfTotal'
	FROM      myTable
	GROUP BY  (YEAR(JoinDate)*100) + MONTH(JoinDate)
	) x

	LEFT JOIN

	(
	SELECT 	(CASE WHEN MONTH(JoinDate) = 12 THEN YEAR(JoinDate) + 1 
			ELSE YEAR(JoinDate) END)*100+
		CASE WHEN MONTH(JoinDate) = 12 THEN 1 
			ELSE MONTH(JoinDate) + 1 END as 'PriorMonthTricked'
		, COUNT(<sign ups>) 'PriorMonthTotal'
	FROM	myTable
	GROUP BY
		(CASE WHEN MONTH(JoinDate) = 12 THEN YEAR(JoinDate) + 1 
			ELSE YEAR(JoinDate) END)*100+
		CASE WHEN MONTH(JoinDate) = 12 THEN 1 
			ELSE MONTH(JoinDate) + 1 END
	) y

	  ON x.ReportMonth = y.PriorMonthTricked

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-17 : 10:57:59

I like tara's idea... and I hate to say a tmp table of sorts may be easier. Maybe even faster with an indexed #myTable:

Declare @myTable Table (cnt int, joinMonth datetime primary key)

Insert Into @myTable
SELECT
cnt = COUNT(UserID),
joinMonth = JoinDate - DAY(JoinDate) + 1
FROM Users
GROUP BY JoinDate - DAY(JoinDate) + 1

Select
A.cnt,
A.joinMonth,
PercentOfTotal = convert(float,A.cnt)/(Select convert(float,sum(cnt)) From @myTable),
PercentGrowth = convert(float,A.cnt - B.cnt)/convert(float,B.cnt)*100
From @myTable as A
Left Join @myTable as B
On A.joinMonth = dateadd(m,-1,b.joinMonth) --get last month's record

Corey

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-06-17 : 14:49:58

First off thanks for all the replies! .. I haven't been able to get Tara's and Seventhnight's solution going exactly as I wanted but that might be my fault.

Drymchaser I've got yours running and yes the columns did line up. However, I'm having difficulty integrating the last steps

The percent of total field is giving me results of 0 down all rows, and I can't figure out exactly how to divide those two results since they aren't actual fields (i think ! ).

Any suggestions to help me finish this one off is greatly appreciated.

Thanks again everyone!

mike123

This is my exact code


SELECT	x.ReportMonth
	, x.CurrentMonthTotal
	, x.PercentOfTotal
	, y.PriorMonthTricked
	, y.PriorMonthTotal
	
FROM	(
	SELECT    (YEAR(date)*100) + MONTH(date) 'ReportMonth'
		, COUNT(userID) 'CurrentMonthTotal'
		, (COUNT(userID) / (select count(userID) from tblUserDetails)* 100.00) 'PercentOfTotal'
	FROM      tblUserDetails
	GROUP BY  (YEAR(date)*100) + MONTH(date)
	) x

	LEFT JOIN

	(
	SELECT 	(CASE WHEN MONTH(date) = 12 THEN YEAR(date) + 1 
			ELSE YEAR(date) END)*100+
		CASE WHEN MONTH(date) = 12 THEN 1 
			ELSE MONTH(date) + 1 END as 'PriorMonthTricked'
		, COUNT(userID) 'PriorMonthTotal'

--, ((  currentmonthtotal / (priormonthtotal * 1.00) - 1) * 100.00) as Growth

	FROM	tblUserDetails
	GROUP BY
		(CASE WHEN MONTH(date) = 12 THEN YEAR(date) + 1 
			ELSE YEAR(date) END)*100 +
		CASE WHEN MONTH(date) = 12 THEN 1 
			ELSE MONTH(date) + 1 END
	) y

	  ON x.ReportMonth = y.PriorMonthTricked


ORDER BY reportMonth desc

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-06-17 : 16:13:32

Replace this:
, (COUNT(userID) / (select count(userID) from tblUserDetails)* 100.00) 'PercentOfTotal'

With this:
, (COUNT(userID)*1.00 / (select count(userID) from tblUserDetails)* 100.00) 'PercentOfTotal'

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-06-17 : 16:21:47

I'd use something like:

CREATE TABLE #hack (
   period	CHAR(6)		NOT NULL
,  new_user	INT		NOT NULL
,  pct_tot	real		NULL
,  pct_growth	real		NULL
   )

INSERT INTO #hack (period, new_user)
   SELECT Convert(CHAR(6), [date], 112), Count(*)
      FROM tblUserDetails
   GROUP BY Convert(CHAR(6), [date], 112)

UPDATE #hack
   SET pct_tot = new_user / (SELECT Cast(Sum(b.new_user) AS real) FROM #hack AS b)
,     pct_growth = new_user / (SELECT Cast(c.new_user AS real)
         FROM #hack AS c
         WHERE  c.period = (SELECT Max(d.period)
            FROM #hack AS d
            WHERE d.period < #hack.period))

SELECT * FROM #hack ORDER BY period

-PatP

Edited to select the final results (oops!)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources