-2

Looking for a simple inline statement (MSSQL) that can return the current workday number of the month (number of days elapsed minus Saturday and Sunday) E.g. NOV 11 2019 would be the 7th working day.

  • 3
    I know you want an inline way, but i'd consider a dimension table for this especially since you said workday and not weekday – scsimon Nov 11 at 21:53
  • use a dimension table as suggested. And if you need a inline way, create a function that calculate the nth working day – Squirrel Nov 12 at 1:42
1

Your title asked for the "nth business day of the current month". And the body of your question flips that around and asks to find the "n" for the current day. I'm gonna go with the question as asked in the body.

Jeff Moden has a really good write-up here. He wrote it as a function, with parameters and error handling and all of that other good stuff. But you want it inline, so we just need to replace the @EndDate pieces with GETDATE(), and replace the @StartDate pieces with a little bit of date math to get the first day of the current month.

SELECT DATEDIFF(day, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0), GETDATE()) + 1 
     - DATEDIFF(week, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0), GETDATE()) * 2
     - (CASE WHEN DATENAME(WEEKDAY, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) = 'Sunday'
          THEN 1 ELSE 0 END)
     - (CASE WHEN DATENAME(WEEKDAY, GETDATE()) = 'Saturday'
          THEN 1 ELSE 0 END) as WorkdayNumber

Because you wanted this as inline code, for the current day, it's not easy to test it for other days. But you can swap @EndDate back in to make it easier to test.

DECLARE @EndDate DATETIME = '20191111'
SELECT DATEDIFF(day, DATEADD(month, DATEDIFF(month, 0, @EndDate), 0), @EndDate) + 1 
     - DATEDIFF(week, DATEADD(month, DATEDIFF(month, 0, @EndDate), 0), @EndDate) * 2
     - (CASE WHEN DATENAME(WEEKDAY, DATEADD(month, DATEDIFF(month, 0, @EndDate), 0)) = 'Sunday'
          THEN 1 ELSE 0 END)
     - (CASE WHEN DATENAME(WEEKDAY, @EndDate) = 'Saturday'
          THEN 1 ELSE 0 END) as WorkdayNumber

Putting in some sample values for @EndDate, you get these results, which seem to meet your expectations.

EndDate      WorkdayNumber
-------      -------------
20191101     1
20191104     2
20191105     3
20191111     7
20191115     11
20191129     21    

Notes:

  • This doesn't contemplate holidays.
  • The DATENAME part depends on your configured language.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.