Microsoft SQL: Rounding time to the nearest quarter hour

A web developer at work wanted to make a change to one of her pages. Instead of calculating the number of hours someone worked after pulling their time from the SQL database, she wanted to know if it could be calculated before the result set was returned. I thought it was a good idea because there may be times when multiple people simultaneously access their time sheets. Calculating the time on the SQL Server would be faster compared to the calculating it on the web server.

The time calculations had to be rounded to the nearest quarter hour in decimal form. An example of what she wanted would be:

7 hours, 29 minutes = 7.50 hours
8 hours, 10 minutes = 8.25 hours
7 hours, 33 minutes = 7.50 hours

All times returned would be after subtracting the time taken for lunch. The table where all of the times are stored is called “EmployeeTime” and is structured like this:

Column	Data Type	Sample Data
EmployeeNumber	Numeric	19946
TimeIn	DateTime	2007-09-19 08:00:00.000
TimeOut	DateTime	2007-09-19 17:00:00.000
Lunch	Decimal(3,2)	1.00

The time taken for lunch is already stored in decimal form. That proved to be very useful in the final solution. Here is a simplified version of the query used (explanation follows):

SELECT EmployeeNumber, (((DATEDIFF(mi,TimeIn,TimeOut)/60) + CASE WHEN (DATEDIFF(mi,TimeIn,TimeOut)%60) BETWEEN 0 AND 7 THEN 0 WHEN (DATEDIFF(mi,TimeIn,TimeOut)%60) BETWEEN 8 AND 22 THEN 0.25 WHEN (DATEDIFF(mi,TimeIn,TimeOut)%60) BETWEEN 23 AND 37 THEN 0.50 WHEN (DATEDIFF(mi,TimeIn,TimeOut)%60) BETWEEN 38 AND 52 THEN 0.75 ELSE 1.00 END) - Lunch) AS WorkHours FROM EmployeeTime

The first DATEDIFF finds the difference between the time in (TimeIn) and the time out (TimeOut) in minutes and divides that number by 60. The result is the whole number of hours (no remainder or decimal).
The CASE expressions use DATEDIFF to find the difference between time in and time out, in minutes, and uses the modulo operator (%) to divide the difference by 60 and return the remainder. That remaining minutes are used to determine what decimal time is added to the number of hours derived in the first step.
Time for lunch is subtracted from the number of total hours for the day (points 1 & 2).

The results were exactly what the web developer wanted. During testing, the total time required to run the query and return almost 5,000 rows of data was consistently less than 10 milliseconds. The employee time sheet pages are displaying faster now that the web server no longer has to perform the calculations. We are now looking at other ways to optimize some of the SQL code to take more of the work load off the web server, where applicable.

Everyone thinks of changing the world, but no one thinks of changing himself. – Leo Nikolaevich Tolstoy

17.Sep.07 Microsoft SQL