Common Table Expressions (CTEs) To The Rescue!

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 28th, 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:

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:

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