Calculating working days between two dates

21

nope, look at the function again, how many parameters does it take. Not the call to the function but the function itself

CREATE FUNCTION

22

I read it many time and understood that the functions waits for 7 parameters not 6
so I've removed the @DayDiff from the function definition and add it to the select as below
declare @DayDiff int
Select @DayDiff = DateDiff(dd, @DateFrom, @DateTo)
and finally it works.
then, tested it again with one day as below
Select * from [dbo].[fn_GetDaysType] ('2020-10-08','B','2020-10-08',1,1,1)
and it gives result (1, 1, 1, 1) while it have to give (1, 1, 0, 0) as before
I think something goes wrong....I'll try to find it out.

1 Like
23

I've reviewed the script many times and don't know why result has been changed from the last time before creating the function
First I’d have to change this part ‘% 7 = 5’ to be’ % 7 = 6’ to correct the Friday counts.
Now we have three scenarios to check the script and to be sure it working accurate

  1. Startdate ‘2020-10-06’ and enddate ‘2020-10-10’ (check all types)
    With @CalendarFunction ‘A’ gives (5, 1, 1, 2) while it have to be (5, 2, 1, 2)
    With @CalendarFunction ‘B’ gives (5, 1, 1, 3) ---ok
  2. Startdate ‘2020-10-08’ and enddate ‘2020-10-10’(check start with holiday)
    With @CalendarFunction ‘A’ gives (3, 1, 1, 0)---I think it’s ok but the total should be ‘2’ not ‘3’
    With @CalendarFunction ‘B’ gives (3, 1, 1, 1)---ok
  3. Startdate ‘2020-10-09’ and enddate ‘2020-10-09’(check with one day)
    With @CalendarFunction ‘A’& ‘B’ gives (1, 0, 1, 0)---ok

Any advice to correct No.1 'A' without change the other results again?
and is my change to modify count Friday is correct?

24

Please post code that is/was working ?

25

here it's, just change '% 7 = 5' to '% 7 = 6' for 'Friday'

Set transaction isolation level read uncommitted
go
declare
@DateFrom DATETIME = '10/6/2020', --@DateFrom - The starting date for your calculation
@CalendarFunction NVARCHAR(1) = 'A', --@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 [DWH_Biostar].[dbo].[HOLIDAYS_LR] h
on h.hdate = DateAdd(day, number, @DateFrom)
and nHoliday = @CalendarFunction

26

Should the returned data types be the following?

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

no, i think could be int for all.

28

Could be? Make that change in the function and see what happens

1 Like
29

And this working code works for the tests you listed out?

30

yes the working code works for the tests.

31

I made the changes for the data type in the function as advised and guess what?
finally it works correctly and passes all the listed tests successfully.
to be truly sure, I'll make some other tests and let you know the results.
thanks a lot 'yosiasz' for your assistance and advises. :hugs:

32

Cool. Dont just copy pasta code posted by others. Always vet it and see if it is accurate

1 Like