Roll Up Query T-SQL.sql

create table callTable (DateCol date, Calls int)
 insert into callTable values
     
 ('1/1/2022',5),
 ('1/2/2022',7),
 ('1/3/2022',9),
 ('1/4/2022',4),
 ('1/5/2022',2),
 ('1/6/2022',5),
 ('1/7/2022',6),
 ('1/8/2022',7),
 ('1/9/2022',9)
    
 ,('1/10/2022',8),
 ('1/11/2022',9),
 ('1/12/2022',1),
 ('1/13/2022',3),
 ('1/14/2022',5),
 ('1/15/2022',7), 
 ('1/16/2022',9),
 ('1/17/2022',4),
 ('1/18/2022',3),
 ('1/19/2022',2) 
    
 ,('2/17/2022',4),
 ('2/18/2022',3),
 ('2/19/2022',2) 
 ;with mycte as (
 select  DateCol,
 DATEPART(yyyy,DateCol) AS yearCol
     ,DATEPART(mm,DateCol) AS monthCol
     ,Datepart(week,DateCol) as wkNum
     ,DATEPART(dd,DateCol) AS dayCol  
     , Calls   
 from callTable )
    
 select  
 case when  dayCol is not null then 'Daily'
  when dayCol is null and wkNum is not null  then 'Weekly'
  when dayCol is null and wkNum is null and monthCol is not null then 'Monthly'
  when dayCol is null and wkNum is null and monthCol is  null then 'Yearly'
 else '' end TrendType,
 Coalesce (  Datefromparts(yearCol,monthCol,dayCol), Try_Cast(Case  
 When monthCol is not null and wkNum is not null then 
 dateadd(wk, wkNum,dateadd(ww, datediff(wk, 0,  dateadd(YEAR, datediff(year,0, getDate()),0)),-1) )
 When monthCol is not null and wkNum is null then 
 Datefromparts(yearCol,monthCol,1)
 When yearCol is not null and  monthCol is  null then 
 Datefromparts(yearCol,1,1)
 else null
 end as Date)) dtCol,
    
 --yearCol,monthCol,wkNum,dayCol,
 SUM(Calls) Total
    
 from mycte 
     
 GROUP BY ROLLUP(yearCol,monthCol,wkNum,dayCol)  
  HAVING (yearCol is not null)
 ORDER BY GROUPING_ID(monthCol  
     ,yearCol,wkNum ,dayCol   )  
     ,yearCol,monthCol,dtCol;  
    
 drop table callTable