Pfthb

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