Number of records by current week

Question

My query returns all currently open records and here is a sample:

BDC_ID	BDC_CREATE_DATE
2660830	5/1/2023
2660846	5/3/2023
2678805	5/3/2023
2698805	5/4/2023
2778545	5/8/2023
2788869	5/9/2023
2798112	5/10/2023
2845531	5/15/2023
2849555	5/17/2023

I am trying to show the ROLLING TOTAL of open records by week, starting with 5/1 being week 1.

So, I would expect the output to look something like this:

WEEK	ROLLING TOTAL
1 (5/1-5/7)	4
2 (5/8-5/14)	7
3 (5/15-5/21)	9

Any insight would be great, thank you.

Answer 1

You should take a look at Microsoft's documentation on SET DATEFIRST and how you can can use that to control the behaviour of some date functions like DATEPART. You can use SET DATEFIRST to set which day is considered the first day of the week.

For example, the below query uses that to set the first day of the week to Monday and then is able to use the DATEPART function like normal. This paired with DENSE_RANK to generate the week number and SUM to calculate the rolling total generate the results you are looking for:

SET DATEFIRST 1; /*monday*/ 

DECLARE @BdcList_TBL TABLE(
    BDC_ID numeric(18,0) NOT NULL 
    ,BDC_CREATE_DATE date NOT NULL 
); 
INSERT INTO @BdcList_TBL (BDC_ID,BDC_CREATE_DATE) VALUES
(2660830,'2023-05-01') 
,(2660846,'2023-05-03') 
,(2678805,'2023-05-03') 
,(2698805,'2023-05-04') 
,(2778545,'2023-05-08') 
,(2788869,'2023-05-09') 
,(2798112,'2023-05-10') 
,(2845531,'2023-05-15') 
,(2849555,'2023-5-17'); 

SELECT 
    CONCAT(CAST(all_weeks.WEEK_NUMBER AS varchar(2)),' (',DATEPART(MONTH,all_weeks.WEEK_START),'/',DATEPART(DAY,all_weeks.WEEK_START),'-',DATEPART(MONTH,all_weeks.WEEK_END),'/',DATEPART(DAY,all_weeks.WEEK_END),')') AS [WEEK]
    ,all_weeks.BDC_COUNT AS ROLLING_TOTAL
FROM 
    /*just doing a subquery for readability in final select for formatting the dates*/
    (SELECT 
        DENSE_RANK() OVER(ORDER BY DATEPART(WEEK,bdc_list.BDC_CREATE_DATE)) AS WEEK_NUMBER 
        ,SUM(COUNT(1)) OVER(ORDER BY DATEPART(WEEK,bdc_list.BDC_CREATE_DATE)) AS BDC_COUNT
        ,DATEADD(DAY, 1 - DATEPART(WEEKDAY, MIN(bdc_list.BDC_CREATE_DATE)), CAST(MIN(bdc_list.BDC_CREATE_DATE) AS DATE)) AS WEEK_START
        ,DATEADD(DAY, 7 - DATEPART(WEEKDAY, MIN(bdc_list.BDC_CREATE_DATE)), CAST(MIN(bdc_list.BDC_CREATE_DATE) AS DATE)) AS WEEK_END 
    FROM 
        @BdcList_TBL AS bdc_list
    GROUP BY 
        DATEPART(WEEK,BDC_CREATE_DATE)) AS all_weeks
ORDER BY 
    all_weeks.WEEK_NUMBER;  

Having a custom date dimension table of sorts would certainly make it easier, but using SET DATEFIRST makes this not much more complex than your average windowed aggregate query.

Hopefully points you in the right direction, and good luck with Epic :)

Answer 2

One way of doing this is by:

• grouping on year and week of year over date
• aggregating on min/max dates, and computing a running sum, on aggregated counts, by ordering on min_date, for the specified groups

SELECT CONCAT(MIN(BDC_CREATE_DATE), ' - ', DATEADD(DAY, 6, MIN(BDC_CREATE_DATE))) AS WEEK,
       SUM(COUNT(BDC_ID)) OVER(ORDER BY MIN(BDC_CREATE_DATE))    AS ROLLING_TOTAL
FROM tab
GROUP BY DATEPART(YEAR, BDC_CREATE_DATE),
         DATEPART(WEEK, BDC_CREATE_DATE)

If your MIN(BDC_CREATE_DATE) does not correspond to the initial part of the week, we can recreate the beginning boundary exploiting the weekday (credits to @LHA for the smart suggestion).

SELECT CONCAT(DATEADD(DAY, 2 - DATEPART(WEEKDAY, MIN(BDC_CREATE_DATE)), MIN(BDC_CREATE_DATE)), 
              ' - ', 
              DATEADD(DAY, 8 - DATEPART(WEEKDAY, MAX(BDC_CREATE_DATE)), MAX(BDC_CREATE_DATE))) AS WEEK,
       SUM(COUNT(BDC_ID)) OVER(ORDER BY MIN(BDC_CREATE_DATE))    AS ROLLING_TOTAL
FROM tab
GROUP BY DATEPART(YEAR, BDC_CREATE_DATE),
         DATEPART(WEEK, BDC_CREATE_DATE)

Output:

WEEK	ROLLING_TOTAL
2023-05-01 - 2023-05-07	4
2023-05-08 - 2023-05-14	7
2023-05-15 - 2023-05-21	9

Check the demo here.