sarpay
4/18/2014 - 3:05 PM

[sql] Calculate Multiple Counts in 1 Query with Date Filtering

[sql] Calculate Multiple Counts in 1 Query with Date Filtering

SELECT SiteID,
	COUNT(ID) TotalUsers,
	SUM(CASE WHEN [RegisterDate] BETWEEN DATEADD(day, -30, GETDATE()) AND GETDATE() THEN 1 ELSE 0 END) PastMonthUsers,
	SUM(CASE WHEN [RegisterDate] BETWEEN DATEADD(day, -7, GETDATE()) AND GETDATE() THEN 1 ELSE 0 END) PastWeekUsers

FROM [Parents]

GROUP BY [SiteID]