T-SQL: Date Ranges
A while ago I pointed out how to solve a problem working with dates in T-SQL vs. SQLCLR solution (The last day of the month). I just wanted to help Ayende to find out an easy set-based solution and to show some disadvantages of "over-using" of SQLCLR when SQL problem could actually be solved without .NET Framework transitions overhead.
I have to admit that Ayende's posts are usually interesting, controversial and challenging. Today he posted another interesting issue - SQL date ranges:
"For instance, how do you find out how many hours each employee worked per day over the last month ?"
Let's see. First of all when we talk about data ranges one can definitely find high performance sql-based solution just because of the fact that MSSQL clustered index is the most optimized kind of solution for this kind of problems. When you fully undestand the above from ideological perspective it will be easier to find out how to do it. Let's assume that each employee writes down his start and end time each day. Raw query should go something like this:
SELECT AVG(DATEDIFF(hh, StartDateTime, EndDateTime)) AS HoursPerDay FROM EmpHours WHERE DATEPART(mm, StartDate) = DATEPART(mm, GETDATE()) - 1 GROUP BY EmpId
Enjoy :)
Thursday, February 16, 2006 9:22 PM
