SQL

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!

SQL Server 2012 introduced a RESULT SET clause to the EXECUTE statement.

It can be used to specify alternate data types and column names for result sets returned by an EXECUTED statement or Stored Procedure.

The examples in this article are based on the AdvetureWorks2014 database.

The following example shows its use with an ad-hoc query example.

Did you know that you can use the SQL Server aggregate functions SUM, COUNT, MAX, MIN and AVG with an OVER Clause now?

Using an OVER clause you can produce individual record values along with aggregate values to different levels, without using a GROUP BY clause. You can also produce running totals, rolling averages, etc.

The examples in this article are based on the AdventureWorks2014 database. 

Aggregate To Different Levels with OVER (PARTITION BY ….)