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
7 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 (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 |