I have a Claim Table in which are saved the Claims received from customers as follows
create table claims(id int identity(1,1), Reception_Date datetime, Fixing_Date datetime)
If the Fixing_Date is Null, it means that the claim is not fixed otherwise it is fixed.
I want to create a stored procedure which returns, for a given year, the following data: The columns returned are
Week number
Date of the end of that week (a Sunday)
Number of received claims
Number of Fixed Claims
Number of non Fixed claims at the end of that week
Example of Data:
insert into claims (reception_date,fixing_date)
values
('02/20/2019 16:15','01/03/2020 17:00'),('01/04/2020 16:15','01/06/2020 17:00'),
('01/09/2020 16:15','09/21/2020 17:00'),('01/10/2020 16:15','10/21/2020 17:00'),
('10/10/2020 16:15','10/25/2020 17:20'),('10/24/2020 16:15','10/29/2020 14:20'),
('10/10/2020 16:15',NULL),('10/30/2020 16:15','10/31/2020 17:20'),
('10/10/2020 16:15','01/11/2020 16:22'),('11/01/2020 16:15','10/17/2020 08:20'),
('02/11/2020 16:15',NULL),('03/11/2020 16:15','10/11/2020 08:00'),
('05/11/2020 16:15',NULL),('05/11/2020 16:15','06/11/2020 11:20'),
('06/11/2020 16:15',NULL)
The procedure I created is the following, if you have a more performant solution please add it
create proc USP_Received_Fixed_NotFixed(@Y as int=2020)
--Kamel Gazzah 29/11/2020
--Stored procedure to get received, fixed and not fixed claims at the end of each week
--Using Claims Tables (create table claims(id int identity(1,1), Reception_Date datetime, Fixing_Date datetime))
as begin
with
CteRecep as
(select year(reception_date) Y,datepart(week,reception_date) W,count(1) N from claims
where year(reception_date)=@y
group by year(reception_date) ,datepart(week,reception_date)),
CteFixed as
(select year(fixing_date) Y,datepart(week,fixing_date) W,count(1) N from CLAIMS
where year(fixing_date)=@y
group by year(fixing_date) ,datepart(week,fixing_date)),
CteDates as
(select cast(cast('01/01/'+cast(@y as varchar(4)) as varchar(20)) as date) d
union all select dateadd(day,1,d) from CteDates where dateadd(day,1,d) <= cast(cast('12/31/'+cast(@y as varchar(4)) as varchar(20)) as date)),
CteWeeks as
(select d , datepart(WEEK,d) W,year(d) Y from CteDates where datepart(weekday,d)=7 ),
CteNotFixed as(
select d [Date],count(1) N from CteWeeks inner join CLAIMS on
(reception_date <= CteWeeks.d) and (reception_date is not null)
and((fixing_date is NULL )or (fixing_date > CteWeeks.d))
group by d) ,
ctereport as(
select
CteWeeks.d DATE,year(CteWeeks.d) Y,datepart(week,CteWeeks.d)W,isnull(CteRecep.N,0)[RECEIVED],isnull(CteFixed.N,0)[FIXED],isnull(CteNotFixed.N,0) [NotFIXED] from CteWeeks
left outer join CteRecep on CteWeeks.w=CteRecep.w and CteRecep.y=CteWeeks.y
left outer join CteFixed on CteWeeks.y=CteFixed.y and CteWeeks.w=CteFixed.w
left outer join CteNotFixed on CteWeeks.y=year(CteNotFixed.date) and CteWeeks.w=datepart(week,CteNotFixed.date))
select Date,W ,Received,Fixed,NotFixed from ctereport
OPTION (MAXRECURSION 0)
end