Calculating working days between two dates

tanehome · October 26, 2020, 2:57pm

Hi all

I've this function is calculating the number of working days between two dates Excluding Weekend & Holiday
and it takes the holidays from table exist in another database (for attendance)
@DateFrom - The starting date for calculation
@CalendarFunction - The holiday type function you want to use as we have two groups type
@DateTo - The end of the date range you want to use
@AdjustMode - Calculate the days between the dates as 0 Exclusive or 1 Inclusive of the first day.
@AdjustWeekend - Excludes weekends from your calculations
@AdjustHolidays - Excludes holidays if the holiday function matches
and it works fine as long as the entered DateFrom is not a Holiday (it counts it as a working day) but I think it's not a big issue as by logic the user shouldn't enter a holiday as a DateFrom to calculate.>>>

the most important issue that I need to modify the this function to use it in another task to check if the (DateFrom) is a Friday or is in the Holiday table by returns '0' if not found or '1' if found (and removing the (DateTo))

I've almost spent two days to change it with no luck

appreciate if anybody can advise what to do...

`>
ALTER FUNCTION [dbo].[fn_GetLeaveDays]
(
@DateFrom DATETIME, --@DateFrom - The starting date for your calculation
@CalendarFunction NVARCHAR(1), --@CalendarFunction - The holiday type function you want to use
@DateTo AS DATETIME, --@DateTo - The end of the date range you want to use
@AdjustMode BIT, --@AdjustMode - Calculate the days between the dates as 0 Exclusive or 1 Inclusive of the first day.
@AdjustWeekEnds BIT, --@AdjustWeekend - Excludes weekends from your calculations
@AdjustHolidays BIT --@AdjustHolidays - Excludes holidays if the holiday function matches
) /@AdjustMode 0=Count whole days only,1=Any day counts as 1/

RETURNS TABLE
--WITH SCHEMABINDING
AS
RETURN (
WITH
cte_tally10 AS (
SELECT number FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally1000 AS (
SELECT 0 AS number UNION ALL SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2 CROSS JOIN cte_tally10 c3
)
SELECT DATEDIFF(DAY, DateFrom, DateTo) + 1 - (1 - @AdjustMode) -
/* subtract holiday days, if specified /
ISNULL((SELECT COUNT()
FROM [DWH_Biostar].[dbo].[HOLIDAYS_LR] HT
WHERE
@AdjustHolidays = 1 AND
HT.hDate >= DATEADD(DAY, 1 - @AdjustMode, check_dates.DateFrom) AND
HT.hDate <= check_dates.DateTo AND
HT.nHoliday = @CalendarFunction
), 0) -
/* subtract Fridays, if specified /
ISNULL((SELECT COUNT()
FROM cte_tally1000 t
WHERE
@AdjustWeekEnds = 1 AND
t.number BETWEEN (1 - @AdjustMode) AND DATEDIFF(DAY, DateFrom, DateTo) AND
DATEDIFF(DAY, 0, DATEADD(DAY, t.number, DateFrom)) % 7 = 4 /Friday/
), 0) AS totaldays
FROM (
SELECT CASE WHEN @DateFrom > @DateTo THEN @DateTo ELSE @DateFrom END AS DateFrom,
CASE WHEN @DateFrom > @DateTo THEN @DateFrom ELSE @DateTo END AS DateTo
) AS check_dates
)

mike01 · October 26, 2020, 3:11pm

Can you provide DDL and sample data. It'll help us help you

tanehome · October 26, 2020, 6:21pm

below is the table creation and sample data for holiday table

CREATE TABLE [dbo].[HOLIDAYS_LR](
[nHoliday] [varchar](1) NULL,
[hDate] [date] NULL,
[hEnddate] [datetime] NULL

) ON [PRIMARY]

INSERT INTO HOLIDAYS_LR (nHoliday, hDate, hEnddate) values ('A', '2020-10-08','2020-10-08 22:00:00.000');
INSERT INTO HOLIDAYS_LR (nHoliday, hDate, hEnddate) values ('A', '2020-10-10','2020-10-10 22:00:00.000');
INSERT INTO HOLIDAYS_LR (nHoliday, hDate, hEnddate) values ('A', '2020-10-24','2020-10-24 22:00:00.000');
INSERT INTO HOLIDAYS_LR (nHoliday, hDate, hEnddate) values ('A','2020-11-21','2020-11-21 22:00:00.000');
INSERT INTO HOLIDAYS_LR (nHoliday, hDate, hEnddate) values ('B', '2020-10-03','2020-10-03 22:00:00.000');
INSERT INTO HOLIDAYS_LR (nHoliday, hDate, hEnddate) values ('B', '2020-10-08','2020-10-08 22:00:00.000');
INSERT INTO HOLIDAYS_LR (nHoliday, hDate, hEnddate) values ('B', '2020-10-17','2020-10-17 22:00:00.000');
INSERT INTO HOLIDAYS_LR (nHoliday, hDate, hEnddate) values ('B','2020-10-31','2020-10-31 22:00:00.000');
INSERT INTO HOLIDAYS_LR (nHoliday, hDate, hEnddate) values ('B','2020-11-14','2020-11-14 22:00:00.000');
INSERT INTO HOLIDAYS_LR (nHoliday, hDate, hEnddate) values ('B','2020-11-28','2020-11-28 22:00:00.000');

mike01 · October 26, 2020, 7:34pm

declare @DateFrom DATETIME = '10/31/2020', --@DateFrom - The starting date for your calculation
@CalendarFunction NVARCHAR(1) = 'B', --@CalendarFunction - The holiday type function you want to use
@DateTo AS DATETIME = '12/31/2020', --@DateTo - The end of the date range you want to use
@AdjustMode BIT = 0, --@AdjustMode - Calculate the days between the dates as 0 Exclusive or 1 Inclusive of the first day.
@AdjustWeekEnds BIT = 1, --@AdjustWeekend - Excludes weekends from your calculations
@AdjustHolidays BIT = 1 --@AdjustHolidays - Excludes holidays if the holiday function matches
,@DayDiff int 

Select @DayDiff = DateDiff(day, @DateFrom, @DateTo) 

IF OBJECT_ID('tempdb..#Tally') IS NOT NULL 
    DROP TABLE #Tally


;WITH
cte_tally10 AS (
SELECT number FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally1000 AS (
SELECT 0 AS number UNION ALL SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2 CROSS JOIN cte_tally10 c3
)

-- tally starts at 0, so we need to remove 1 from DayDiff
select * into #Tally 
  from cte_tally1000
where number <= @DayDiff - 1


select count(1) as TotalDays, 
	sum(case when @AdjustHolidays = 1 
			   AND hDate >= DATEADD(DAY, 1 - @AdjustMode, @DateFrom) 
			   AND hDate <= @DateTo
			   AND nHoliday = @CalendarFunction then 1 else 0 end) as Holidays
	,sum(case when @AdjustWeekEnds = 1 
			AND n.number BETWEEN (1 - @AdjustMode) AND DATEDIFF(DAY, @DateFrom, @DateTo) 
			AND DATEDIFF(DAY, 0, DATEADD(DAY, n.number, @DateFrom)) % 7 = 4 
			and h.hdate is null then 1 else 0 end) as Weekends-- /Friday/
	,count(1)  - 
	sum(case when @AdjustHolidays = 1 
			   AND hDate >= DATEADD(DAY, 1 - @AdjustMode, @DateFrom) 
			   AND hDate <= @DateTo
			   AND nHoliday = @CalendarFunction then 1 else 0 end) -
	sum(case when @AdjustWeekEnds = 1 
			AND n.number BETWEEN (1 - @AdjustMode) AND DATEDIFF(DAY, @DateFrom, @DateTo) 
			AND DATEDIFF(DAY, 0, DATEADD(DAY, n.number, @DateFrom)) % 7 = 4 
			and h.hdate is null then 1 else 0 end) as TotalDays-- /Friday/
  from  #Tally N
	left join [dbo].[HOLIDAYS_LR] h
		on h.hdate = DateAdd(day, number, @DateFrom)

tanehome · October 27, 2020, 11:14am

Thank you mike01 for your time and reply

I’ve tested the script using from date 10/06/2020 and to date 10/10/2020 and found

The result is same for both CalendarFunction A & B (5, 1, 1, 3)

TotalDays Holidays Weekends TotalDays(workdays)

5 1 1 3

while it have to be (5, 2, 1 , 2) for A and (5, 1, 1, 3) for B

also, when I put date in the format dd/mm/yyyy gives me error and null values I should use mm/dd/yyyy while user will enter the format yyyy/mm/dd
when I select one day(same day) in from and to it gives result null while it have to give (1,0,0,1) if no holiday or weekend

mike01 · October 27, 2020, 2:24pm

We don't want to count dates twice, so 10/10 is showing as a holiday. So we don't want to count it as a weekend too. Also, need to add AND nHoliday = @CalendarFunction to each case statement

tanehome · October 28, 2020, 8:55am

i've added it to each case but the result still not match the supposed result as it gives (5,1,0,4) for both calenderfunction A & B

mike01 · October 28, 2020, 2:16pm

I think this provides what are looking for

Set transaction isolation level read uncommitted
go
declare @DateFrom DATETIME = '10/6/2020', --@DateFrom - The starting date for your calculation
@CalendarFunction NVARCHAR(1) = 'B', --@CalendarFunction - The holiday type function you want to use
@DateTo AS DATETIME = '10/10/2020', --@DateTo - The end of the date range you want to use
@AdjustMode BIT = 1, --@AdjustMode - Calculate the days between the dates as 0 Exclusive or 1 Inclusive of the first day.
@AdjustWeekEnds BIT = 1, --@AdjustWeekend - Excludes weekends from your calculations
@AdjustHolidays BIT = 1 --@AdjustHolidays - Excludes holidays if the holiday function matches
,@DayDiff int 

Select @DayDiff = DateDiff(day, @DateFrom, @DateTo) 

IF OBJECT_ID('tempdb..#Tally') IS NOT NULL 
    DROP TABLE #Tally


;WITH
cte_tally10 AS (
SELECT number FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally1000 AS (
SELECT 0 AS number UNION ALL SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2 CROSS JOIN cte_tally10 c3
)

-- tally starts at 0, so we need to remove 1 from DayDiff
select * into #Tally 
  from cte_tally1000
where number <= @DayDiff 


select
	count(1) - (1 - @AdjustMode) as TotalDays, 
	sum(case when @AdjustHolidays = 1 
			   AND hDate is not null then 1 else 0 end) as Holidays	,
	sum(case when @AdjustWeekEnds = 1 
			AND DatePart(dw, DateAdd(day, n.number, @DateFrom)) % 7 = 5  
			 then 1 else 0 end)  as Fridays
	,count(1) - (1 - @AdjustMode) - 
	sum(case when @AdjustHolidays = 1 
			   AND hDate is not null then 1 else 0 end) -
	sum(case when @AdjustWeekEnds = 1 
			AND DatePart(dw, DateAdd(day, n.number, @DateFrom)) % 7 = 5 then 1 else 0 end) as TotalDays-- /Friday/
  from  #Tally N
	left join [dbo].[HOLIDAYS_LR] h
		on h.hdate = DateAdd(day, number, @DateFrom)
	   and  nHoliday = @CalendarFunction

tanehome · October 28, 2020, 3:09pm

this exactly what I need, it works perfect
thank you 'mike01' so much, appreciate your assistance.

tanehome · October 28, 2020, 3:16pm

only one issue ...is there a way to force the variable date format to be dd/mm/yyyy

mike01 · October 28, 2020, 6:31pm

that's for display only. Dates are mm/dd/yyyy unless you change the regional settings to be EU

tanehome · October 28, 2020, 7:57pm

I've put the script in the form of 'table function' again put it gives me many errors, I've tried to fix with no luck

Set transaction isolation level read uncommitted
go
create FUNCTION [dbo].[fn_GetDaysType]
(
@DateFrom DATETIME, --@DateFrom - The starting date for your calculation
@CalendarFunction NVARCHAR(1), --@CalendarFunction - The holiday type function you want to use
@DateTo AS DATETIME, --@DateTo - The end of the date range you want to use
@AdjustMode BIT, --@AdjustMode - Calculate the days between the dates as 0 Exclusive or 1 Inclusive of the first day.
@AdjustWeekEnds BIT, --@AdjustWeekend - Excludes weekends from your calculations
@AdjustHolidays BIT, --@AdjustHolidays - Excludes holidays if the holiday function matches
@DayDiff int
) /@AdjustMode 0=Count whole days only,1=Any day counts as 1/

RETURNS TABLE

AS
RETURN (

Select @DayDiff = DateDiff(day, @DateFrom, @DateTo)

IF OBJECT_ID('tempdb..#Tally') IS NOT NULL
DROP TABLE #Tally

;WITH
cte_tally10 AS (
SELECT number FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally1000 AS (
SELECT 0 AS number UNION ALL SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2 CROSS JOIN cte_tally10 c3
)

-- tally starts at 0, so we need to remove 1 from DayDiff
select * into #Tally
from cte_tally1000
where number <= @DayDiff

select
count(1) - (1 - @AdjustMode) as TotalDays,
sum(case when @AdjustHolidays = 1
AND hDate is not null then 1 else 0 end) as Holidays ,
sum(case when @AdjustWeekEnds = 1
AND DatePart(dw, DateAdd(day, n.number, @DateFrom)) % 7 = 5
then 1 else 0 end) as Fridays
,count(1) - (1 - @AdjustMode) -
sum(case when @AdjustHolidays = 1
AND hDate is not null then 1 else 0 end) -
sum(case when @AdjustWeekEnds = 1
AND DatePart(dw, DateAdd(day, n.number, @DateFrom)) % 7 = 5 then 1 else 0 end) as TotalWorkDays-- /Friday/
from #Tally N
left join [DWH_Biostar].[dbo].[HOLIDAYS_LR] h
on h.hdate = DateAdd(day, number, @DateFrom)
and nHoliday = @CalendarFunction
)

tanehome · October 28, 2020, 7:58pm

the errors:
Msg 102, Level 15, State 1, Procedure fn_GetDaysType, Line 17
Incorrect syntax near '='.
Msg 2772, Level 16, State 1, Procedure fn_GetDaysType, Line 22
Cannot access temporary tables from within a function.
Msg 2772, Level 16, State 1, Procedure fn_GetDaysType, Line 33
Cannot access temporary tables from within a function.
Msg 2772, Level 16, State 1, Procedure fn_GetDaysType, Line 48
Cannot access temporary tables from within a function.
Msg 102, Level 15, State 1, Procedure fn_GetDaysType, Line 52
Incorrect syntax near ')'.

yosiasz · October 28, 2020, 8:33pm

this should be easy for you to fix

Cannot access temporary tables from within a function.
Msg 2772, Level 16, State 1, Procedure fn_GetDaysType, Line 33
Cannot access temporary tables from within a function.
Msg 2772, Level 16, State 1, Procedure fn_GetDaysType, Line 48
Cannot access temporary tables from within a function.
Msg 102, Level 15, State 1, Procedure fn_GetDaysType, Line 52

tanehome · October 29, 2020, 7:57am

could I replace the temp table with a permanent one or something else?

yosiasz · October 30, 2020, 3:09pm

maybe make it a sub select or trim down the unneeded further up the chain

yosiasz · October 30, 2020, 3:36pm

use sqlteam
go

create FUNCTION [dbo].[fn_GetDaysType]
(
@DateFrom DATETIME, --@DateFrom - The starting date for your calculation
@CalendarFunction NVARCHAR(1), --@CalendarFunction - The holiday type function you want to use
@DateTo AS DATETIME, --@DateTo - The end of the date range you want to use
@AdjustMode BIT, --@AdjustMode - Calculate the days between the dates as 0 Exclusive or 1 Inclusive of the first day.
@AdjustWeekEnds BIT, --@AdjustWeekend - Excludes weekends from your calculations
@AdjustHolidays BIT, --@AdjustHolidays - Excludes holidays if the holiday function matches
@DayDiff int
) /*@AdjustMode 0=Count whole days only,1=Any day counts as 1*/

RETURNS @workingdays TABLE (
        TotalDays int,
        Holidays bit,
        Fridays bit,
        TotalWorkDays int
    )

AS

begin

	Select @DayDiff = DateDiff(dd, @DateFrom, @DateTo)

	declare @cte_tally10 table(number int);
	declare @cte_tally1000 table(number int);

	insert into @cte_tally10
	SELECT number FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)

	insert into @cte_tally1000
	SELECT 0 AS number UNION ALL 
	SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
	  FROM @cte_tally10 c1 
	 CROSS JOIN @cte_tally10 c2 CROSS JOIN @cte_tally10 c3

    insert into @workingdays
	select TotalDays = count(1) - (1 - @AdjustMode),
	       Holidays = sum(case when @AdjustHolidays = 1 
		                  AND hDate is not null then 1 else 0 end) ,
	       Fridays = sum(case when @AdjustWeekEnds = 1
	                     AND DatePart(dw, DateAdd(day, n.number, @DateFrom)) % 7 = 5
			             then 1 else 0 end),
			TotalWorkDays = count(1) - (1 - @AdjustMode) -
			                sum(case when @AdjustHolidays = 1
			                    AND hDate is not null then 1 else 0 end) -
			                sum(case when @AdjustWeekEnds = 1
			                AND DatePart(dw, DateAdd(day, n.number, @DateFrom)) % 7 = 5 then 1 else 0 end)-- /Friday/
	from (select * from @cte_tally1000 where number <= @DayDiff	) N
	left join [dbo].[HOLIDAYS_LR] h
	on h.hdate = DateAdd(day, number, @DateFrom)
	and nHoliday = @CalendarFunction

	RETURN;
end

tanehome · October 30, 2020, 4:48pm

thank you 'yosiasz' for your assist
I've used the script and the function created successfully
then i tested it using the below command
Select * from [dbo].[fn_GetDaysType] ('2020-10-06','B','2020-10-10',1,1,1)
but actually it gives me another error
"An insufficient number of arguments were supplied for the procedure or function dbo.fn_GetDaysType."

I've tried to change the date format but same error.
Select * from [dbo].[fn_GetDaysType] ('10/06/2020','B','10/10/2020',1,1,1)

yosiasz · October 30, 2020, 4:59pm

you are going to have to do a little bit of work on this one. how many parameters does the function have and compare that to how many parameters you are sending

tanehome · October 30, 2020, 5:13pm

I've tried my best with no luck
it have 6 parameters and I put them, the last one (the seventh) @DayDiff int
as I understood it takes the value from the select statement
Select @DayDiff = DateDiff(dd, @DateFrom, @DateTo)
am I right?