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;

Answer 2

As noted in a comment, I would highly recommend that you build yourself a calendar table. They are very useful, and pretty much the first thing I add to any database I build. With one in place your query becomes a simple count:

SELECT  COUNT(*)
FROM    dbo.Calendar AS c
WHERE   c.TheDayName = @Holiday
AND     c.TheMonth = @Month
AND     c.TheYear = @Year
AND     c.TheDate >= @EmployeeStartDate;

If you can't/won't do that, then the next best thing is to generate a list of dates yourself (without a loop), then you can filter that and count the days that meet your criteria, e.g.

CREATE FUNCTION dbo.GetTotalWeekHoliday
    (
        @year SMALLINT,
        @Month TINYINT,
        @joindate DATE,
        @DayName VARCHAR(10)
    )
RETURNS TABLE
AS
RETURN
(   WITH Dates AS
    (   SELECT  Date = DATEADD(DAY ,ROW_NUMBER() OVER(ORDER BY n1.N) - 1, ms.MonthStart),ms.MonthStart      
        FROM    (VALUES (DATEFROMPARTS(@year, @month, 1))) AS ms (MonthStart)
        -- Cross join to 8x4 rows (32) to ensure full month
        CROSS JOIN (VALUES (1), (1), (1), (1)) AS n1 (N)
        CROSS JOIN (VALUES (1), (1), (1), (1), (1), (1), (1), (1)) AS n2 (N)
    )
    SELECT  Holidays = COUNT(*)
    FROM    Dates AS d
    WHERE   DATENAME(WEEKDAY, d.Date) = @DayName
    AND     d.Date >=ISNULL(@JoinDate, '19000101')
    AND     d.Date < EOMONTH(d.MonthStart)
);

Note I've converted this to a TVF as this is not inlinable as a scalar UDF, so this will be more efficient when used. An example use of this would be:

SELECT *
FROM (VALUES 
  (1, 2024, '20231223'), 
  (1, 2024, '20240120')
  ) as d (Month, Year, StartDate)
CROSS APPLY dbo.GetTotalWeekHoliday(d.Year, d.Month, d.StartDate,'Tuesday') AS h;

But if you need to call like a scalar function for any reason you can do so as follows:

SELECT *,
   Holidays = (SELECT h.Holidays FROM dbo.GetTotalWeekHoliday(d.Year, d.Month, d.StartDate,'Tuesday') AS h)
FROM (VALUES 
  (1, 2024, '20231223'), 
  (1, 2024, '20240120')
  ) as d (Month, Year, StartDate);

Example on db<>fiddle