Grouping sets by year, month, and date

Question

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)

Answer 1

You could use:

;WITH cte AS (
  select
    productLine, 
    employeeId, 
    salesDate, 
    mon=month(salesdate),
    yr=year(salesdate),
    salesTotal
  from #tbl_data   
)
select
    productLine, 
    employeeId, 
    salesDate, 
    mon,
    yr ,
    sum(salesTotal) as salesTotal
from cte
group by grouping sets
(
     (productLine, employeeId, salesDate, yr)
    ,(productLine, employeeId, yr)
    ,(productLine)
    ,(productLine, mon, yr)
);

Rextester Demo