SQL query group by multiple time periods

I have the following table:

SalesDate smalldatetime,
ProductId varchar(20),
QuantitySold tinyint,
Price smallmoney

I want to get the last 1m, 3m, 1y total quantity sold for each product. How do I do that in one query? My attempt so far is to run three separate queries, one for each time period, and then combine them using UNION. Is there a better way? I am using MS SQL 2008.

1 Answer

You can more easily do this with multiple columns:

select productId, sum(case when salesdate >= dateadd(month, -1, getdate()) then quantitysold else 0 end) as qs1m, sum(case when salesdate >= dateadd(month, -3, getdate()) then quantitysold else 0 end) as qs3m sum(case when salesdate >= dateadd(month, -12, getdate()) then quantitysold else 0 end) as qs12m
from table t
group by productId;

(Note: You probably want to at least remove the times from getdate(), but you might have an entirely different way of getting the "as-of" date.)

Because the time periods overlap, it is easier to do this with columns (and conditional aggregation) than with a separate row for each time period.