SQL server 2012 - group by month, 3 month, 6 months, 11 months and prior year 11 months

Question

Trying to build a query to have sum on sales column by month, 3 months, 6 months, 11 months and prior year 11 months. What are possible options to go about it. I tried datediff and date related functions didn't get intended results.

Would like some suggestions on how to go about it?

Answer 1

This should get you started.

CREATE TABLE sales (
    product varchar (1),
    month int,
    amount int
)
insert into sales values ('a',1,5); insert into sales values ('a',1,33); 
insert into sales values ('a',2,32); insert into sales values ('b',1,12); 
insert into sales values ('b',2,4); insert into sales values ('c',1,5);
insert into sales values ('c',2,11);  insert into sales values ('c',2,13);

SELECT 
    product,
    SUM(CASE WHEN month = 1 THEN amount END) AS Month1,
    SUM(CASE WHEN month = 2 THEN amount END) AS Month2
FROM
    sales
GROUP BY
    product

output:

product   | Month1   |  Month2   
--------------------------------
    a     |   38     |   32   
    b     |   12     |   4
    c     |   5      |   24