We need to sum up the elapsed hours of a given table of task data by date and task. Each task has a start and finish datetime.
Please see Sum elapsed hours by task by date, part 2 of 3 for the explanation of which dates we're going to GROUP BY, and which portions of each task we're going to SUM() for each task.
Now it's all over but the shouting. Here's the final solution, as developed with Microsoft SQL Server syntax:
select taskday, task , sum( datediff(s , case when taskday >= start then taskday else start end , case when finish <= dateadd(d,1,taskday) then finish else dateadd(d,1,taskday) end ) ) / 3600.0 as hrs from ( select distinct cast(convert(char(10) ,dateadd(d,i,start),120) as datetime ) as taskday from integers inner join tasktimes on cast(convert(char(10) ,dateadd(d,i,start),120) as datetime ) between cast(convert(char(10) ,start,120) as datetime ) and finish ) as distinctdates inner join tasktimes on start <= dateadd(d,1,taskday) and finish > taskday group by taskday, task order by taskday, task
Several explanations are in order concerning the specific SQL Server syntax used here.
The CASE expressions are standard SQL that SQL Server happens to support. Other databases may have other constructions (e.g. IIF in Microsoft Access).
DATEDIFF(datepart, datetime, datetime) and DATEADD(datepart, number, datetime) perform date arithmetic, and dateparts s and d represent seconds and days, respectively. We SUM() the elapsed times in seconds, and then divide the total by 3600 to get hours. Calculating the difference in hours would also be possible, but requires care to ensure fractional hours are not lost.
CONVERT(CHAR(10),datetime,120) strips off the time portion of a datetime value, and results in a string, but CAST(expression as DATETIME) converts the string back to a datetime. This is the midnight function mentioned earlier.
Whether any of you other than the person who submitted the original question can use this final solution is not important. What this 3-part explanation demonstrates, I hope, is how to attack questions of this type, and the importance of having comprehensive test data.
Dig Deeper on SQL Server Database Modeling and Design
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.