SQL Server Row_Number() sequence not skipping NULL or 0 values to calculate working days

Question

I am attempting to create a Working Days field in an existing date dimension in SQL Server to extract the nth working day of a month. I have researched solutions so far and have created a field for Working Day where weekend days= 0 and Weekdays=1. I have used Row_Number () to generate a sequence to indicate the nth working day. However my issue is Row_Number () sequence isn't calculating as expected as it doesn't skip the 0 values and continues sequence. Anyone have an ideas to fix? Thank you.

SELECT 
    DATE_KEY_YYYYMM,
    [Date KEY],
    [Day of Week Short Name],
    [Day of Week Number],
    CASE 
        WHEN [Day of Week Number] IN (1, 2, 3, 4, 5) 
            THEN 1 
            ELSE 0 
    END AS BusinessDay,
    CASE 
        WHEN [Day of Week Number] IN (6, 7) 
            THEN NULL 
            ELSE ROW_NUMBER() OVER (PARTITION BY [Date_Key_YYYYMM] ORDER BY [Date Key]) 
    END AS [RowCount]
FROM
    [DIM].[Call Date]

Output

Date Key YYYY	Date	Day of Week	WorkingDay	RowCount
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

Correct output - I need this:

Date Key YYYY	Date	Day of Week	WorkingDay	RowCount
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	5

Answer 1

It might be a more intuitive solution to simply do what you're saying: skip the calculation for rows that aren't business days, but use SUM() OVER(). e.g.:

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

Output:

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

• Example db<>fiddle

(I didn't need the PARTITION BY given the sample data, but you'd add it if you need it.)

Though some suggestions:

• Why are you calculating business day (and count of business days) in your queries? This could be a simple computed column in the dimension table. Or manually applied exactly once, since weekdays never change, but also, you might want to account for government holidays, business closures, etc.

• Try to avoid keywords (like Date) and names that otherwise violate rules for identifiers.