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 

Answer 2

Don't recalculate your week table on each execute, rather build a permanent date table and just reuse it. On my side your query takes on average 140ms and with the permanent date table it takes 6ms

Also don't rely so heavily on implicit converts, DATE to DATETIME will be implicitly converted and slow down your query. It could also make your query non-SARGABLE which would ignore indexes and result in table scans.

Here is a basic dates table population script, for this demonstration I'm leaving it as a temp table, it would be best for this to be created as an actual indexed table

DECLARE @StartDate DATETIME = '2020-01-01 00:00:00'
DECLARE @EndDate DATETIME = '2021-01-01 00:00:00'

IF OBJECT_ID('tempdb..#Dates') IS NOT NULL DROP TABLE #Dates
CREATE TABLE #Dates (
    DateKey INT,
    WeekId INT,
    WeekStartDate DATE,
    StartDate DATETIME,
    EndDate DATETIME,
    DayNumber INT
)

WHILE (@StartDate < @EndDate)
BEGIN

INSERT INTO #Dates VALUES (FORMAT(@StartDate,'yyyyMMdd'), 
       DATEPART(WEEK,@StartDate),      
       DATEADD(DAY , 7-DATEPART(WEEKDAY,@StartDate),@StartDate),
       @StartDate,
       DATEADD(SECOND,-1,DATEADD(DAY,1,@StartDate)),
       DATEPART(WEEKDAY,@StartDate))

SET @StartDate = DATEADD(DAY,1,@StartDate)

END

Once you have your dates table you can start leveraging it, this query runs on average 4 to 6ms

 DECLARE @year INT = 2020
 DECLARE @ClaimStartDate DATETIME = CONVERT(VARCHAR(4),@year)+'-Jan-01'
 DECLARE @ClaimEndDate DATETIME = DATEADD(SECOND,-1,DATEADD(YEAR,1,@ClaimStartDate))
 
 ;WITH WeekBase
 AS
 (SELECT D.WeekStartDate,
         D.WeekId
 FROM #Dates D
 WHERE D.StartDate BETWEEN @ClaimStartDate AND @ClaimEndDate
 GROUP BY D.WeekStartDate,
          D.WeekId),
 NotFixed
 AS
 (SELECT d.WeekStartDate,
       COUNT(1) NotFixedCount
 FROM WeekBase d
     INNER JOIN #CLAIMS ON reception_date < d.WeekStartDate
                        AND reception_date IS NOT NULL
                        AND (fixing_date IS NULL OR fixing_date > d.WeekStartDate)
 GROUP BY d.WeekStartDate),
 Fix
 AS 
 (SELECT D.WeekStartDate AS WeekStartDate,
         COUNT(1) AS FixedCount
 FROM #Dates D
      INNER JOIN #Claims C ON C.fixing_date BETWEEN D.StartDate AND D.EndDate
 WHERE D.StartDate BETWEEN @ClaimStartDate AND @ClaimEndDate
 GROUP BY D.WeekStartDate),
 Received
 AS
 (SELECT D.WeekStartDate AS WeekStartDate,
         COUNT(1) AS ReceivedCount
 FROM #Dates D
      INNER JOIN #Claims C ON C.reception_date BETWEEN D.StartDate AND D.EndDate
 WHERE D.StartDate BETWEEN @ClaimStartDate AND @ClaimEndDate
 GROUP BY D.WeekStartDate)
 SELECT D.WeekStartDate AS WeekStartDate,
        D.WeekId AS W,
        ISNULL(REC.ReceivedCount,0) AS Received,
       ISNULL(FIX.FixedCount,0) AS Fixed,
       ISNULL(NF.NotFixedCount,0) AS NotFixed
 FROM WeekBase D
      LEFT JOIN Received REC ON REC.WeekStartDate = D.WeekStartDate
     LEFT JOIN Fix FIX ON FIX.WeekStartDate = D.WeekStartDate
     LEFT JOIN NotFixed NF ON NF.WeekStartDate = D.WeekStartDate