SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tab(
    BDC_ID               VARCHAR(30), 	
    BDC_CREATE_DATE      DATE
);
INSERT INTO tab VALUES
('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');

SELECT * FROM tab


BDC_ID
BDC_CREATE_DATE


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-05-17


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)


WEEK
ROLLING_TOTAL


2023-05-01 - 2023-05-07
4

2023-05-08 - 2023-05-14
7

2023-05-15 - 2023-05-21
9


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)

 
  


WEEK
ROLLING_TOTAL


2023-05-01 - 2023-05-07
4

2023-05-08 - 2023-05-14
7

2023-05-15 - 2023-05-21
9

BDC_ID	BDC_CREATE_DATE
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-05-17

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