`[Table Detailsenter image description here]

Expected Output, when passed parameters – Month asint and Year as int

Display given month attandace report date wise in pivot, I am unable to set or update the values in dynamic dates as “Leave” or ‘L’ when employee has taken leaves in that month.

DECLARE @Year INT = 2022;
DECLARE @Month INT = 11;

DECLARE @StartDate DATE = DATEFROMPARTS(@Year, @Month, 1);
DECLARE @EndDate DATE = EOMONTH(@StartDate);

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

CREATE TABLE #DynamicDates (DynamicDate DATE);

DECLARE @currentDate DATE = @StartDate;
WHILE @currentDate <= @EndDate
    BEGIN
        INSERT INTO #DynamicDates
            (
                DynamicDate
            )
        VALUES
            (
                @currentDate
            );
        SET @currentDate = DATEADD(DAY, 1, @currentDate);
    END

DECLARE @Columns NVARCHAR(MAX) = N'';
DECLARE @ColumnISNULL NVARCHAR(MAX) = N'';
DECLARE @sql NVARCHAR(MAX) = N'';

SELECT
    @Columns = STRING_AGG(QUOTENAME(CONVERT(VARCHAR(10), DynamicDate, 23)), ','),
    @ColumnISNULL
             = STRING_AGG(
                             'ISNULL(' + QUOTENAME(CONVERT(VARCHAR(10), DynamicDate, 23)) + ', 0) AS '
                             + QUOTENAME(CONVERT(VARCHAR(10), DynamicDate, 23)), ','
                         )
FROM
    #DynamicDates;

SET @sql
    = '
DECLARE @StartDate DATE = ''' + CONVERT(VARCHAR(10), @StartDate, 23) + ''';
DECLARE @EndDate DATE = ''' + CONVERT(VARCHAR(10), @EndDate, 23)
      + ''';

SELECT
    aid,
    empname,
    @StartDate AS StartDate,
    @EndDate AS EndDate,
    AttendanceDate,
    ' + @Columns + ',
    ' + @ColumnISNULL
      + '
FROM
(
    SELECT
        e.empname,
        CONCAT(a.Adate, '' '', temp.DynamicDate) AS AttendanceDate,
        CASE WHEN COUNT(DISTINCT CONCAT(a.Adate, '' '', temp.DynamicDate)) > 0 THEN ''Present'' ELSE ''Absent'' END AS att_count
    FROM
        dbo.employee e
        LEFT JOIN dbo.empattendance a ON e.aid = a.EmpAid
                             
        LEFT JOIN #DynamicDates temp ON temp.DynamicDate = a.Adate
    GROUP BY
        e.aid,
        e.empname,
        CONCAT(a.Adate, '' '', temp.DynamicDate)
) src
PIVOT
(
    MAX(att_count)  
    FOR AttendanceDate IN (' + @Columns + ')
) AS piv';

PRINT @sql;

EXEC sp_executesql
    @sql

DROP TABLE #DynamicDates;

I have tried so far, Unable to

How to set values of L and H , for dynamic dates

`