Grouping sets by year, month, and date

Viewed 870

Is it possible to rollup the date, month, and year using GROUPING SETS, where the dates are shownn and grouped into month, and year, and the value for month and year are displayed instead of being null?

Here is the sample data, and my attempt at using grouping sets

create table #tbl_data
(
employeeId int
,productLine int
,salesDate datetime
,salesTotal money
)

insert into #tbl_data
values
(1,1,'02/09/2017',199)
,(1,1,'04/10/2017',379)
,(2,1,'04/21/2017',323)
,(2,1,'04/10/2017',461)
,(2,1,'06/11/2017',304)
,(3,1,'01/28/2017',147)
,(3,1,'02/09/2017',320)
,(4,1,'03/07/2017',344)
,(4,1,'03/13/2017',176)

select
    productLine, 
    employeeId, 
    salesDate, 
    mon=month(salesdate),
    yr=year(salesdate), 
    sum(salesTotal) as salesTotal
from 
    #tbl_data

group by grouping sets
(
    (productLine, employeeId, salesDate)
    ,(productLine, employeeId)
    ,(productLine)
)

Here is what returned by the query (left) and what i wanted to accomplish (right)

enter image description here

1 Answers
Related