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 1 of 2 for the complete problem statement.
Here again is the timeline diagram, with the portion of the task elapsed time that occurs between taskday and nextday shown in bold red:
taskday nextday | | 1 <--S====F | | | | 2 <------S==|=F | | | 3 <------S==|============|=F | | 4 |<--S====F | | | 5 |<--------S==|=F | | 6 | |<--S====F | |
To handle all of the requirements for the different dates that we will need to GROUP BY, we join an integers table to each task, to "generate" each date between the date of start and finish.
select distinct midnight(start+i) as taskday from integers inner join tasktimes on midnight(start+i) between midnight(start) and finish
Here, midnight is "pseudo-SQL" and represents a datetime value with the time portion removed (or zeroed out, if you prefer). We furthermore pretend that we can simply add an integer number to a datetime value without a care, to get a new datetime value which is that number of days later. Note: the integers table column i must contain values 0, 1, 2, and so on. Especially 0.
Now that we can GROUP BY every date of interest, let us move on to the summing. In scenarios 2 and 3, we include only the elapsed time from taskday, up to either finish or nextday, whichever comes first. In scenarios 4 and 5, we include only the elapsed time from start, up to either finish or nextday, whichever comes first.
Stated another way, we sum only the portion of elapsed time between the greater of taskday and start, and the lesser of finish and nextday. You may have to look at the diagram for a while to see it this way.
sum( lesser(finish,nextday) - greater(taskday,start) )
Here again, lesser and greater are "pseudo-SQL" functions. And again, we will pretend that we can obtain elapsed times in hours simply by subtracting datetime values without a care. In practice, the syntax needed to calculate an elapsed time in hours will vary wildly from one database system to the next.
Please proceed to Sum elapsed hours by task by date, part 3 of 3.
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.