[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]