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()

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

)