How to get weekly and Monthly view from daily in sql server

Question

Hello All,

I have a table like below.
I need to get daily,weekly and monthly calls data from this.

My out put would be like below

Could any one please help what is the query to write here

Answer 1

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