CODEWITHSUNDEEP
sqlsql-serversql-server-2008datetime
jsingh
jsingh

Reputation: 209

Calculate total and elapsed number of working days in the month for a given date

I am stuck with a SQL query.

I am trying to calculate two different things in a same query:

  1. Number of business days in a month (this will exclude weekends).
  2. How many days working days have been passed in a month.

Let's say for November (as on 11/9/2018)

no.of business days        no. of business days passed
22                                7

I tried like this :

WITH cteAllDates AS 
(
    DECLARE @StartDate DATETIME
    DECLARE @EndDate DATETIME

    SET @StartDate = '10/01/2018'
    SET @EndDate = '10/31/2018'

    SELECT
        (DATEDIFF(dd, @StartDate, @EndDate) + 1)
        - (DATEDIFF(wk, @StartDate, @EndDate) * 2)
        - (CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) 
        - (CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) AS x
) AS y
SELECT x
FROM cteAllDates

I would like to create a virtual table so that I can use these fields in my complete query. And if its possible I can do this with GETDATE() and not to declare dates every time.

Upvotes: 0

Views: 639

Answers (2)

TomC
TomC

Reputation: 2814

Since you want to do it in a CTE, and based on the earlier answer you found on SO, here is a version that does it all for the current month without needing to define start and end dates:

EDIT: To create a holiday table

First, create a holiday table. Don't do this every time, make it a persistent table, and keep it filled up with all holidays you need - easter, xmas etc.

create table holidays(holiday date)
insert holidays values ('2018-09-23'),('2018-09-24')

Now the query, including the check for number of holidays between the dates

;with dates as(
    select dateadd(d,-day(getdate())+1,convert(date,getdate())) as startofmonth,
    dateadd(d,-1,dateadd(m,1,dateadd(d,-day(getdate())+1,convert(date,getdate())))) as endofmonth,
    convert(date,getdate()) as today
)
,holidaycount as (
    select count(*) as holidaysinmonth,
        sum(case when holiday<=today then 1 else 0 end) as holidaystodate
    from dates
    join holidays on holiday between startofmonth and endofmonth
)
,daycounts as(
    select dates.*,

       (DATEDIFF(dd, startofmonth, endofmonth) + 1)
      -(DATEDIFF(wk, startofmonth, endofmonth) * 2)
      -(CASE WHEN DATENAME(dw, startofmonth) = 'Sunday' THEN 1 ELSE 0 END) 
      -(CASE WHEN DATENAME(dw, endofmonth) = 'Saturday' THEN 1 ELSE 0 END)
      -isnull(holidaysinmonth,0) as wkdaysinmonth,

       (DATEDIFF(dd, startofmonth, today) + 1)
      -(DATEDIFF(wk, startofmonth, today) * 2)
      -(CASE WHEN DATENAME(dw, startofmonth) = 'Sunday' THEN 1 ELSE 0 END) 
      -(CASE WHEN DATENAME(dw, today) = 'Saturday' THEN 1 ELSE 0 END)
      -isnull(holidaystodate,0) as wkdaystodate

    from dates
    cross join holidaycount
) 

select * from daycounts

EDIT: If you cant create a temp table, add this as an additional CTE before the holidaycount one like so:

,holidays as (
    select holiday from (values ('2018-11-23'),('2018-11-24')) t(holiday)
)
,holidaycount as (

Upvotes: 1

D-Shih
D-Shih

Reputation: 46249

You can try to use cte recursive make a calendar table, then use condition aggregate function to get your result.

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = DATEADD(DAY, 1, EOMONTH(GETDATE(), -1));
SET @EndDate = DATEADD(DAY, 1, EOMONTH(GETDATE()));

;WITH CTE AS (
    select @StartDate startdt,@EndDate enddt
    UNION ALL
    SELECT  DATEADD (day ,1 , startdt) , @EndDate
    FROM CTE
    WHERE DATEADD (day,1,startdt) <= @EndDate
)


select SUM(CASE WHEN DATENAME(dw, startdt) NOT IN ('Sunday','Saturday') THEN 1 END) 'no.of business days',
       SUM(CASE WHEN DATENAME(dw, startdt) NOT IN ('Sunday','Saturday') AND GETDATE() >= startdt THEN 1 END) 'no. of business days passed'
FROM CTE

sqlfiddle

Result

no.of business days     no. of business days passed
22                      7

Upvotes: 0

Related Questions