Wsrtjtyk

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.

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 (

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

You can do this without recursive CTE as long as you want data for a single month.

• There are 20 working days in first 28 days on month. Just check 29, 30 and 31


• There are 5 working days in first 7 days, 10 in 14 and so on. Just check 6 days.

DECLARE @dt AS DATE = '2018-11-09';

WITH vars1 AS (

    SELECT

        d1 = DATEADD(dd, 1, EOMONTH(@dt, -1)),

        dn = EOMONTH(@dt),

        wks = DAY(@dt) / 7

), vars AS (

    SELECT

        d1, -- first day of month

        dn, -- last day of month

        wks, -- number of 7-day intervals elapsed

        d29 = DATEADD(dd, 28, d1), -- 29th day of month

        d30 = DATEADD(dd, 29, d1),

        d31 = DATEADD(dd, 30, d1),

        dp1 = DATEADD(dd, wks * 7 + 0, d1), -- wks * 7 gives you 0, 7, 14, 21 or 28

        dp2 = DATEADD(dd, wks * 7 + 1, d1), -- wks * 7 + 0 ... 5 are the dates to check

        dp3 = DATEADD(dd, wks * 7 + 2, d1),

        dp4 = DATEADD(dd, wks * 7 + 3, d1),

        dp5 = DATEADD(dd, wks * 7 + 4, d1),

        dp6 = DATEADD(dd, wks * 7 + 5, d1)

    FROM vars1

)

SELECT 

    [no. of business days] = 20 + 

    IIF(d29 <= dn AND DATENAME(dw, d29) NOT IN ('Saturday', 'Sunday'), 1, 0) + 

    IIF(d30 <= dn AND DATENAME(dw, d30) NOT IN ('Saturday', 'Sunday'), 1, 0) + 

    IIF(d31 <= dn AND DATENAME(dw, d31) NOT IN ('Saturday', 'Sunday'), 1, 0),

    [no. of business days passed] = wks * 5 +

    IIF(dp1 <= @dt AND DATENAME(dw, dp1) NOT IN ('Saturday', 'Sunday'), 1, 0) + 

    IIF(dp2 <= @dt AND DATENAME(dw, dp2) NOT IN ('Saturday', 'Sunday'), 1, 0) + 

    IIF(dp3 <= @dt AND DATENAME(dw, dp3) NOT IN ('Saturday', 'Sunday'), 1, 0) + 

    IIF(dp4 <= @dt AND DATENAME(dw, dp4) NOT IN ('Saturday', 'Sunday'), 1, 0) + 

    IIF(dp5 <= @dt AND DATENAME(dw, dp5) NOT IN ('Saturday', 'Sunday'), 1, 0) + 

    IIF(dp6 <= @dt AND DATENAME(dw, dp6) NOT IN ('Saturday', 'Sunday'), 1, 0)

FROM vars

DB Fiddle