By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.[Date Dim]([Date Key YYYY] char(6),
[Date] date, [Day of Week] varchar(10));
INSERT dbo.[Date Dim] VALUES
('201404','20140401','Tues'), -- 1 1
('201404','20140402','Wed'), -- 1 2
('201404','20140403','Thurs'), -- 1 3
('201404','20140404','Fri'), -- 1 4
('201404','20140405','Sat'), -- 0 NULL
('201404','20140406','Sun'), -- 0 NULL
('201404','20140407','Mon'); -- 1 7 -- 5
INSERT dbo.[Date Dim] VALUES
('201405','20140501','Thurs'), -- 1 1
('201405','20140502','Fri'), -- 1 2
('201405','20140503','Sat'), -- 0 NULL
('201405','20140504','Sun'), -- 0 NULL
('201405','20140505','Mon'), -- 1 3
('201405','20140506','Tue'), -- 1 4
('201405','20140507','Wed'); -- 1 5
14 rows affected
;WITH src AS
(
SELECT [Date Key YYYY], [Date], [Day of Week],
BusinessDay = CASE WHEN DATEPART(WEEKDAY, [Date]) IN (1,7)
THEN 0 ELSE 1 END
FROM [Date Dim]
)
SELECT [Date Key YYYY], [Date], [Day of Week], BusinessDay,
[RowCount] = CASE WHEN BusinessDay = 1 THEN SUM(BusinessDay)
OVER (PARTITION BY [Date Key YYYY] ORDER BY [Date]) END
FROM src;
| Date Key YYYY | Date | Day of Week | BusinessDay | RowCount |
|---|---|---|---|---|
| 201404 | 2014-04-01 | Tues | 1 | 1 |
| 201404 | 2014-04-02 | Wed | 1 | 2 |
| 201404 | 2014-04-03 | Thurs | 1 | 3 |
| 201404 | 2014-04-04 | Fri | 1 | 4 |
| 201404 | 2014-04-05 | Sat | 0 | null |
| 201404 | 2014-04-06 | Sun | 0 | null |
| 201404 | 2014-04-07 | Mon | 1 | 5 |
| 201405 | 2014-05-01 | Thurs | 1 | 1 |
| 201405 | 2014-05-02 | Fri | 1 | 2 |
| 201405 | 2014-05-03 | Sat | 0 | null |
| 201405 | 2014-05-04 | Sun | 0 | null |
| 201405 | 2014-05-05 | Mon | 1 | 3 |
| 201405 | 2014-05-06 | Tue | 1 | 4 |
| 201405 | 2014-05-07 | Wed | 1 | 5 |