How to calculate Employee Weekly OFF in a month

Question

I wrote funtion for this which is working fine but there is one issue if employee joining mid of month or last week so how to count his/her weekly holidays.

Currently below function getting his/her all Holidays in a month.

GO
/****** Object:  UserDefinedFunction [dbo].[GetTotalWeekHoliday]    Script Date: 1/23/2024 12:12:04 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetTotalWeekHoliday]
    (
        @Month AS nvarchar(10), -- parameter a
        @date AS date, -- parameter b
        @count1 AS INT,
        @HolidayName AS nvarchar(50),
        @year AS nvarchar(10)
    )
    RETURNS INT -- return type
    AS
    BEGIN
    set @date=@year+'-'+@Month+'-'+'01'
    while DATEPART(Month,@date)=@Month
Begin
    --Select DATEPART(DAY,@date);
    --Select DATEPART( WEEKDAY, @DATE )
    DECLARE @Name VARCHAR(20)

    SELECT  @Name = CASE ( DATEPART(dw, @Date) + @@DATEFIRST ) % 7
                         WHEN 1 THEN 'Sunday'
                         WHEN 2 THEN 'Monday'
                         WHEN 3 THEN 'Tuesday'
                         WHEN 4 THEN 'Wednesday'
                         WHEN 5 THEN 'Thursday'
                         WHEN 6 THEN 'Friday'
                         WHEN 0 THEN 'Saturday'
                       END 
    If @Name=@HolidayName
    Begin
        
        --Insert Data Into Your Table
        set @count1+=1
    End
    
    Set @date=DATEADD(Day,1,@date); 
End
        RETURN @count1 -- return statement
    END;

Example Current month employee1 holiday name ='Tuesday' result will be "5" but if employee1 appointed 20th january then i want to show result "2" currently its showing all "5"

Answer 1

function for employees joining mid-month I added a new parameter `@JoiningDate, to specify their start date. The function's loop now includes a check to ensure it only counts holidays that fall on or after this joining date. Additionally, the function internally determines the first day of the given month

ALTER FUNCTION [dbo].[GetTotalWeekHoliday]
(
    ...
    @JoiningDate AS date  -- New parameter for employee's joining date
    ...
)
...
BEGIN
    ...
    while DATEPART(Month, @date) = CONVERT(int, @Month) AND @date <= EOMONTH(@date)
    BEGIN
        ...
        -- Check if the date is a holiday and is on or after the joining date
        IF @DayName = @HolidayName AND @date >= @JoiningDate
        BEGIN
            set @count1 += 1
        END
        ...
    END
    ...
END;