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

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.

Answer 2

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 3

with const as (
    -- using a fixed date reference
    -- this could easily be a parameter, subquery, or relative date calculation
    select cast('20230501' as date) as basedate
), data as (
    select *, datediff(day, basedate, BDC_CREATE_DATE) / 7 as wk
    from T t cross join const
)
select
    wk, dateadd(day, wk * 7, basedate), dateadd(day, wk * 7 + 6, basedate),
    sum(count(*)) over (order by wk) as ROLLING_TOTAL
from data
group by wk;

This makes no use of SQL Server week functions. It's just using an arbitrary date as a starting point. There is no need to assume any particular dates within a week are present.

Answer 4

Count the rows from the table group by the date of preceding Monday and the first Monday date gets 1 for ROW_NUMBER() OVER(ORDER BY Monday date).

WITH MONDAYS AS(SELECT DATEADD(DAY,(2-7-(@@DATEFIRST+DATEPART(WEEKDAY,BDC_CREATE_DATE))%7)%7,BDC_CREATE_DATE)MONDAY,COUNT(*)COUNTER
FROM BDC_TABLE GROUP BY DATEADD(DAY,(2-7-(@@DATEFIRST+DATEPART(WEEKDAY,BDC_CREATE_DATE))%7)%7,BDC_CREATE_DATE))
SELECT CONCAT(ROW_NUMBER()OVER(ORDER BY MONDAY),'(',MONTH(MONDAY),'/',DAY(MONDAY),'-',MONTH(DATEADD(DAY,6,MONDAY)),'/',DAY(DATEADD(DAY,6,MONDAY)),')')WEEK,SUM(COUNTER)OVER(ORDER BY MONDAY)[ROLLING TOTAL]
FROM MONDAYS