Q

Sum elapsed hours by task by date, part 3 of 3

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.

This was last published in November 2004

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close