help with sproc ( sum by month step 2)

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 step 2)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-06-23 : 18:43:51

I have the following procedure that helps me with some reporting data.

It lets me know how many users have signed up for each month, as well as the % of each month against the total. I need to take the reporting a couple steps further. I need to know the following and would really appreciate any further help to get me in the right direction.

I have the total for each Month of all accounts
I need to add another total for all accounts (WHERE active ='1')

I also need to add another total "MasterTotal" which is the total of two tables accounts. (Active Users and deleted Users). I have another table which is the same table structure, but it is named "tblUsers_Deleted".

How Can I join onto this table? Is the base structure of this SPROC good for adding these additional queries? I am still going to need to add a couple more after this.

Thanks ALOT for any assistance.

Mike123


SELECT	x.ReportMonth
	, x.CurrentMonthTotal
	, x.PercentOfTotal
	, y.PriorMonthTricked
	, y.PriorMonthTotal
	
FROM	(
	SELECT    (YEAR(date)*100) + MONTH(date) 'ReportMonth'
		, COUNT(userID) 'CurrentMonthTotal'
		, (COUNT(userID)*1.00 / (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'

--, ((  cast(currentmonthtotal as integer) / (cast(priormonthtotal as integer) * 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

Here's some dummy data:
ReportMonth / CurrentMonthTotal / %total / PriorMnthTricked / PriorMnthTotal

200204 1643 1.482169759400000 200204 1465
200203 1465 1.321593851200000 200203 1439
200202 1439 1.298138943260000 200202 1304
200201 1304 1.176353844340000 200201 905
200112 905 .816411218660000 200112 622
200111 622 .561113566860000 200111 603

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-06-24 : 09:28:08

More derived tables, for the first part.

SELECT	x.ReportMonth
	, x.CurrentMonthTotal
	, x.PercentOfTotal
	, y.PriorMonthTricked
	, y.PriorMonthTotal
	, z.CurrentMonthActiveTotal
	
FROM	(
	SELECT    (YEAR(date)*100) + MONTH(date) 'ReportMonth'
		, COUNT(userID) 'CurrentMonthTotal'
		, (COUNT(userID)*1.00 / (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'

--, ((  cast(currentmonthtotal as integer) / (cast(priormonthtotal as integer) * 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
	
	LEFT JOIN
	(
	SELECT    (YEAR(date)*100) + MONTH(date) 'ReportMonth'
		, COUNT(userID) 'CurrentMonthActiveTotal'

	FROM      tblUserDetails
	WHERE	  Active = '1'
	GROUP BY  (YEAR(date)*100) + MONTH(date)
	) z

	  ON x.ReportMonth = z.ReportMonth

ORDER BY reportMonth desc

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-06-24 : 19:36:44

thanks drymchaser! that worked...

I've managed to add quite alot onto the SPROC by adding a few more of those derived tables.

I have one more question that I think will allow me to figure out the rest of what I need to do.

I Want to create a column named "MasterTotal" which is the sum of two columns that I have in my result. The two columns that I need to add are "CurrentMonthTotal" and "CurrentMonthTotal_Deleted"

How Can I do this? I've include my code below, any help is greatly appreciated.

Thanks,
mike123


SELECT	x.ReportMonth
	, x.CurrentMonthTotal
	, x.PercentOfTotal
	, y.PriorMonthTricked
	, y.PriorMonthTotal
	, z.total_Active_UserCount
	, rp3.total_AwaitingValidation_UserCount
	, rp4.total_Deleting_UserCount
	, rp5.total_RE_Verification_UserCount
	, rp6.total_UnVerified_UserCount
	, rp7.currentMonthTotal_Deleted
	--, rp8.currentMonth_MasterTotal
	
FROM	(
	SELECT    (YEAR(date)*100) + MONTH(date) 'ReportMonth'
		, COUNT(userID) 'CurrentMonthTotal'
		, (COUNT(userID)*1.00 / (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'

--, ((  cast(currentmonthtotal as integer) / (cast(priormonthtotal as integer) * 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


	
	LEFT JOIN
	(
	SELECT    (YEAR(date)*100) + MONTH(date) 'ReportMonth2'
		,COUNT(userID) 'total_Active_UserCount'

	FROM      tblUserDetails
	WHERE	  Active = '1'
	GROUP BY  (YEAR(date)*100) + MONTH(date)
	) z

	  ON x.ReportMonth = z.ReportMonth2


	LEFT JOIN
	(
	SELECT    (YEAR(date)*100) + MONTH(date) 'ReportMonth3'
		,COUNT(userID) 'total_AwaitingValidation_UserCount'

	FROM      tblUserDetails
	WHERE	  Active = '2'
	GROUP BY  (YEAR(date)*100) + MONTH(date)
	) rp3

	  ON x.ReportMonth = rp3.ReportMonth3


	LEFT JOIN
	(
	SELECT    (YEAR(date)*100) + MONTH(date) 'ReportMonth4'
		,COUNT(userID) 'total_Deleting_UserCount'

	FROM      tblUserDetails
	WHERE	  Active = '3'
	GROUP BY  (YEAR(date)*100) + MONTH(date)
	) rp4

	  ON x.ReportMonth = rp4.ReportMonth4


	LEFT JOIN
	(
	SELECT    (YEAR(date)*100) + MONTH(date) 'ReportMonth5'
		,COUNT(userID) 'total_RE_Verification_UserCount'

	FROM      tblUserDetails
	WHERE	  Active = '8'
	GROUP BY  (YEAR(date)*100) + MONTH(date)
	) rp5

	  ON x.ReportMonth = rp5.ReportMonth5


	LEFT JOIN
	(
	SELECT    (YEAR(date)*100) + MONTH(date) 'ReportMonth6'
		,COUNT(userID) 'total_UnVerified_UserCount'

	FROM      tblUserDetails
	WHERE	  Active = '9'
	GROUP BY  (YEAR(date)*100) + MONTH(date)
	) rp6

	ON x.ReportMonth = rp6.ReportMonth6



	LEFT JOIN
	(
	SELECT    (YEAR(date)*100) + MONTH(date) 'ReportMonth7'
		,COUNT(userID) 'currentMonthTotal_Deleted'

	FROM      tblDeletedUsers
--	WHERE	  Active = '9'
	GROUP BY  (YEAR(date)*100) + MONTH(date)
	) rp7

	ON x.ReportMonth = rp7.ReportMonth7


ORDER BY reportMonth desc

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-06-25 : 09:01:27

If you got 'em you can use 'em.


SELECT	x.ReportMonth
	, x.CurrentMonthTotal
	, x.PercentOfTotal
	, y.PriorMonthTricked
	, y.PriorMonthTotal
	, z.total_Active_UserCount
	, rp3.total_AwaitingValidation_UserCount
	, rp4.total_Deleting_UserCount
	, rp5.total_RE_Verification_UserCount
	, rp6.total_UnVerified_UserCount
	, rp7.currentMonthTotal_Deleted
        , COALESCE(x.CurrentMonthTotal, 0) + 
         COALESCE(rp7.currentMonthTotal_Deleted, 0) 'currentMonth_MasterTotal'

...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources