CODEWITHSUNDEEP
sqlsql-serverrow-number
SarahChapman
SarahChapman

Reputation: 65

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

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

Upvotes: 1

Views: 77

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

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

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

Upvotes: 2

Related Questions