Krishnandu Sarkar Krishnandu Sarkar - 2 months ago 15
SQL Question

Displaying Monthly Attendance Date Wise

I need to display attendance data in monthly format using PIVOT. But unable to figure out how it should be done.

Below is my table structure

Attendance:

AttendanceID EmployeeID AttendanceDateTime


Employee:

EmployeeID EmployeeName


Holiday:

HolidayID HolidayDate


Leave:

LeaveID EmployeeID LeaveDateTime IsApproved


I want to display result like below from the data provided in above table

EmployeeName 01-09-2016 02-09-2016 03-09-2016 04-09-2016
A Present Absent Holiday Leave

ZLK ZLK
Answer

So the first thing you probably want to do before creating a pivot on dates is figure out what dates you want to pivot. For the purposes of an example, I've just included a way you could get every date within a certain range in this answer but it really depends on what you're looking for.

SELECT *
FROM (
    SELECT E.EmployeeID, E.EmployeeName, T.DateToCheck, COALESCE(H.val, A.val, L.val, 'Absent') val
    FROM tblEmployee E
    CROSS JOIN (
        SELECT CAST(DATEADD(DAY, number, '2016-09-01') AS DATE)
        FROM master..spt_values
        WHERE type = 'P'
        AND number <= 3) T(DateToCheck)
    LEFT JOIN (SELECT 'Holiday' val, HolidayDate FROM tblHoliday) H ON H.HolidayDate = T.DateToCheck
    LEFT JOIN (SELECT 'Present' val, AttendanceDateTime, EmployeeID FROM tblAttendance) A ON CAST(A.AttendanceDateTime AS DATE) = T.DateToCheck AND A.EmployeeID = E.EmployeeID
    LEFT JOIN (SELECT 'Leave' val, LeaveDateTime, EmployeeID FROM tblLeave) L ON CAST(L.LeaveDateTime AS DATE) = T.DateToCheck AND L.EmployeeID = E.EmployeeID) T
PIVOT (MAX(val) FOR DateToCheck IN ([2016-09-01], [2016-09-02], [2016-09-03], [2016-09-04])) P;

The basic logic here is that you want to produce your dates to check, compare those dates to each of the different tables (using left joins or outer applies), get only one result (the logic in this answer uses a coalesce to decide which value it'll show), then pivot the result.

EDIT: If you require a PIVOT for a dynamic set of column names (e.g. a dynamic date range), you're going to need to use dynamic SQL. Here's one way you could do it:

DECLARE @SQL VARCHAR(MAX) = '', @dateRange VARCHAR(MAX) = '', @startDate DATE = '2016-09-01', @endDate DATE = '2016-09-05';

SELECT @dateRange += ',' + QUOTENAME(DATEADD(DAY, number, @startDate))
FROM master..spt_values 
WHERE type = 'P'
AND DATEADD(DAY, number, @startDate) <= @endDate;

SELECT @dateRange = STUFF(@dateRange, 1, 1, '');

SELECT @SQL = 'SELECT *
FROM (
    SELECT E.EmployeeID, E.EmployeeName, T.DateToCheck, COALESCE(H.val, A.val, L.val, ''Absent'') val
    FROM tblEmployee E
    CROSS JOIN (
        SELECT CAST(DATEADD(DAY, number, ''' + CAST(@startDate AS CHAR(10)) + ''') AS DATE)
        FROM master..spt_values
        WHERE type = ''P''
        AND DATEADD(DAY, number, ''' + CAST(@startDate AS CHAR(10)) + ''') <= ''' + CAST(@endDate AS CHAR(10)) + ''') T(DateToCheck)
    LEFT JOIN (SELECT ''Holiday'' val, HolidayDate FROM tblHoliday) H ON H.HolidayDate = T.DateToCheck
    LEFT JOIN (SELECT ''Present'' val, AttendanceDateTime, EmployeeID FROM tblAttendance) A ON CAST(A.AttendanceDateTime AS DATE) = T.DateToCheck AND A.EmployeeID = E.EmployeeID
    LEFT JOIN (SELECT ''Leave'' val, LeaveDateTime, EmployeeID FROM tblLeave) L ON CAST(L.LeaveDateTime AS DATE) = T.DateToCheck AND L.EmployeeID = E.EmployeeID) T
PIVOT (MAX(val) FOR DateToCheck IN (' + @dateRange + ')) P;';

PRINT @SQL;
EXEC(@SQL);

Changing the start and end dates will change the output. You can use the PRINT @SQL to see the actual query.