T-SQL Work days in current quarter and work days passed in current quarter

Question

What would the best method be to pull the number of Work days (Monday-Friday) in the current quarter in addition to how many work days have passed so far in the current quarter? This is for T-SQL/MS SQL

I have current total days, just need to remove weekends...

datediff(dd, dateadd(qq, datediff(qq,0, getdate()),0), dateadd(qq, datediff(qq,0, getdate()) + 1 ,0)) as [Days in Quarter]

You existing query tells me that is T-SQL which is either MS SQL Server or perhaps Sybase. When creating a query ALWAYS stipulate the database as a tag. "SQL" is a query standard, not a database name. — Paul Maxwell, Nov 17 '18 at 00:20
Calendar table. When you say work days, do you just mean Monday-Friday? What about holidays? https://stackoverflow.com/questions/1201874/calendar-table-for-data-warehouse — Stuart Ainsworth, Nov 17 '18 at 00:54

Paul Maxwell · Answer 1 · 2018-11-17T00:52:10.530

A way to do this is as follows:

select
       dates.*

     , (DATEDIFF(dd, startofperiod, endofperiod) + 1)
      -(DATEDIFF(wk, startofperiod, endofperiod) * 2)
      -(CASE WHEN DATENAME(dw, startofperiod) = 'Sunday' THEN 1 ELSE 0 END) 
      -(CASE WHEN DATENAME(dw, endofperiod) = 'Saturday' THEN 1 ELSE 0 END)
      as wkdaysinperiod

     , (DATEDIFF(dd, startofperiod, today) + 1)
      -(DATEDIFF(wk, startofperiod, today) * 2)
      -(CASE WHEN DATENAME(dw, startofperiod) = 'Sunday' THEN 1 ELSE 0 END) 
      -(CASE WHEN DATENAME(dw, today) = 'Saturday' THEN 1 ELSE 0 END)
      as wkdaystodate
from (
    select 
          dateadd(qq, datediff(qq,0, getdate()),0)                     as startofperiod
        , dateadd(dd,-1,dateadd(qq, datediff(qq,0, getdate()) + 1 ,0)) as endofperiod
        , convert(date,getdate()) as today
    ) as Dates
;

The method seen above assumes the use of English, so if that does not suit modulus can be used to remove that dependency, which is shown below.

The "base date" in SQL Server is 1900-01-01 which is a Monday, so the remaining number of days since that date after division by 7, 0 is Monday, 1, Tuesday, ... 6 Sunday. This is true regardless of server datefirst settings.

select
       dates.*, ca.*

     , (DATEDIFF(dd, startofperiod, endofperiod) + 1)
      -(DATEDIFF(wk, startofperiod, endofperiod) * 2)
      -(CASE WHEN periodstartdaynum = 6 THEN 1 ELSE 0 END) /* Sunday */
      -(CASE WHEN periodenddaynum   = 5 THEN 1 ELSE 0 END) /* Saturday */
      as wkdaysinperiod

     , (DATEDIFF(dd, startofperiod, today) + 1)
      -(DATEDIFF(wk, startofperiod, today) * 2)
      -(CASE WHEN periodstartdaynum = 6 THEN 1 ELSE 0 END) /* Sunday */
      -(CASE WHEN todaydaynum       = 5 THEN 1 ELSE 0 END) /* Saturday */
      as wkdaystodate
from (
    select 
          dateadd(qq, datediff(qq,0, getdate()),0)                     as startofperiod
        , dateadd(dd,-1,dateadd(qq, datediff(qq,0, getdate()) + 1 ,0)) as endofperiod
        , convert(date,getdate()) as today
    ) as Dates
cross apply (
    select
          datediff(dd,0,startofperiod) % 7 as periodstartdaynum
        , datediff(dd,0,endofperiod) % 7   as periodenddaynum
        , datediff(dd,0,today) % 7         as todaydaynum
    ) ca
;

T-SQL Work days in current quarter and work days passed in current quarter

1 Answers1