Common Table Expressions (CTEs) To The Rescue!

By mandy.doward

Tags:

SQL, SQL Server, CTE, WITH, Query 0 Comments

I was recently asked by a delegate how they could calculate lost work hours due to sick days on a per month basis. This might sound fairly straight forward, but their database only stored the start date for the sickness period and the number of days for the sickness period. So, that means that if an employee was off sick for a period of 5 days starting October 28^th, 3 of the days should be counted into October and 2 of the days should be counted in November. Not so simple after all!

So how can we solve this one? Well Common Table Expressions are really useful in scenarios like this.

First we will create some sample data to work with:

USE AdventureWorks
GO

SELECT EmployeeID, HireDate As SickLeaveDate, SickLeaveHours, CAST(100000/SickLeaveHours * 10 AS SMALLMONEY) AS Salary
INTO EmployeeAbsence
FROM HumanResources.Employee

-- Display the data
SELECT * FROM EmployeeAbsence

Here is an extract of the generated table data.

We will assume there are 248 working days in a year, and 8 hours in a work day.

Creating A Common Table Expression (CTE)

Here you go, a Common Table Expression (CTE) solution providing us woth both an annual and monthly break down of hours lost and cost to the business:

;WITH CTE_AbsenceData (EmployeeID, SickLeaveStartDate, 
				SickLeaveHourDateTime, SickLeaveHours, 
				SicknessYear, SicknessMonth, HourlyPay, Counter)
AS
	(
    SELECT EmployeeID, SickLeaveDate, SickLeaveDate, 
                  SickLeaveHours, YEAR(SickLeaveDate), MONTH(SickLeaveDate), Salary/248/8, 1
	FROM EmployeeAbsence

	UNION ALL

		SELECT cte.EmployeeID, SickLeaveStartDate, 
                      DATEADD(HOUR,1,SickLeaveHourDateTime), cte.SickLeaveHours, 
                     YEAR(SickLeaveHourDateTime), MONTH(SickLeaveHourDateTime), Salary/248/8, Counter + 1
		FROM EmployeeAbsence AS ea
			JOIN CTE_AbsenceData AS cte 
                ON ea.EmployeeID = cte.EmployeeID
				AND cte.SickLeaveStartDate = ea.SickLeaveDate
				AND cte.Counter < cte.SickLeaveHours
	)	
SELECT SicknessYear, SicknessMonth, 
	         SUM(HourlyPay) AS 'Total Cost Of Sickness Absence', 
             COUNT(*) AS 'Total Hours Lost To Sickness'
FROM CTE_AbsenceData
GROUP BY
GROUPING SETS ((SicknessYear), (SicknessYear, SicknessMonth))
ORDER BY SicknessYear, SicknessMonth

Here is an extract from the results:

To find out more about CTEs and advanced querying techniques take a look here:

http://www.ptr.co.uk/sql-server-courses.htm

Common Table Expressions (CTEs) To The Rescue!

You are here