Caculate the number of not fixed claims at the end of each week based on the dates of reception and fixing

Question

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

1. Week number

2. Date of the end of that week (a Sunday)

3. Number of received claims

4. Number of Fixed Claims

5. 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)

Answer 1

The procedure I created is the following, if you have a more performant solution please add it

Link to SQL FIDDLE

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