Scribd
Upload
4 views2 pages

SQL Dynamic Pivot for Employee Hours

The document contains a SQL script that generates a report of hours worked by employees over a specified date range. It creates a list of months, prepares raw data by aggregating hours per employee, and then pivots this data to display total hours worked per month for each employee. Finally, it cleans up temporary tables used during the process.

Uploaded by

sudharlwc
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
4 views2 pages

SQL Dynamic Pivot for Employee Hours

The document contains a SQL script that generates a report of hours worked by employees over a specified date range. It creates a list of months, prepares raw data by aggregating hours per employee, and then pivots this data to display total hours worked per month for each employee. Finally, it cleans up temporary tables used during the process.

Uploaded by

sudharlwc
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

DECLARE @FromDate DATE = '2024-01-01',

@ToDate DATE = '2024-05-31',


@UserID INT = NULL,
@TaskOrder NVARCHAR(50) = NULL,
@DeptId INT = 1; --------------------------------------------------------- -- 1.
Build Month List ---------------------------------------------------------
;WITH MonthRange AS
(
SELECT DATEFROMPARTS(YEAR(@FromDate), MONTH(@FromDate), 1) AS Dt
UNION ALL
SELECT DATEADD(MONTH, 1, Dt) FROM MonthRange
WHERE DATEADD(MONTH, 1, Dt) <= DATEFROMPARTS(YEAR(@ToDate), MONTH(@ToDate), 1) )

SELECT Dt INTO #MonthList FROM MonthRange OPTION (MAXRECURSION 0);

--------------------------------------------------------- -- 2. Dynamic Month


Columns ---------------------------------------------------------
DECLARE @MonthCols NVARCHAR(MAX);
SELECT @MonthCols = STRING_AGG(QUOTENAME(CONVERT(CHAR(6), Dt,112)), ',') FROM
#MonthList;

SET @MonthCols = LEFT(@MonthCols, Len(@MonthCols)-1);


--------------------------------------------------------- -- 3. Prepare Raw Data
(PER-MONTH PER-EMPLOYEE) ---------------------------------------------------------
SELECT [Link], [Link] + ' ' + [Link] AS EmployeeName, [Link],
FORMAT([Link],'MMMyyyy') AS MonthLabel, SUM([Link]) AS Hours
INTO #RawData FROM SecurityUsers U
INNER JOIN UserDetails d ON [Link] = [Link]
CROSS JOIN #MonthList M
LEFT JOIN TimeSheet T ON [Link] = [Link]
LEFT JOIN TimeSheetEntry E ON
[Link] = [Link] AND
[Link] BETWEEN @FromDate AND @ToDate
AND YEAR([Link]) = YEAR([Link])
AND MONTH([Link]) = MONTH([Link])
WHERE [Link] = @DeptId AND
[Link] = 4 AND
(@UserID IS NULL OR [Link] = @UserID) AND
(@TaskOrder IS NULL OR [Link] LIKE '%' + @TaskOrder + '%')
GROUP BY [Link], [Link], [Link], [Link], [Link];
--------------------------------------------------------- -- 4. Dynamic Pivot
(PER-MONTH TOTALS PER EMPLOYEE)
---------------------------------------------------------

DECLARE @TotalHours NVARCHAR(MAX)


SELECT @TotalHours = STRING_AGG(ISNULL('+ LTRIM(RTRIM(value))+ ',0)','+')
FROM STRING_SPLTI( @MonthCols, ',');

DECLARE @SQL NVARCHAR(MAX) =


' SELECT EmployeeName, TaskOrderNumber, ' + @MonthCols + ', '+ @TotalHours + ' As
TotalHours
FROM ( SELECT EmployeeName, TaskOrderNumber, MonthLabel, Hours FROM #RawData ) src

PIVOT ( SUM(Hours) FOR MonthLabel IN (' + @MonthCols + ') ) p


ORDER BY EmployeeName; ';
EXEC sp_executesql @SQL;
--------------------------------------------------------- -- Cleanup
---------------------------------------------------------
DROP TABLE #MonthList;
DROP TABLE #RawData;

You might also like