jeudi 30 avril 2015

SQL Minute Total by Hour

I have two fields in a data table - "startTime" and "endTime." These two fields represent a duration of time the user spent on a particular task. These are varchar fields. So, let's say we have a startTime of "21:05:00" and an endTime of "22:09:00." I need the code to sum the total number of minutes spent in hour 21, and the 22 hundred hour separately (i.e. 9 minutes). So, not just a simple minute difference, but a breakdown by hour.

What might be the best way to do that?

Thus far, I have created a table that will return all possible hours in a 24-hour period. Here's a sample:

Hour    startTime                endTime
0       2015-01-01 00:00:00.000  2015-01-01 01:00:00.000
1       2015-01-01 01:00:00.000  2015-01-01 02:00:00.000
2       2015-01-01 02:00:00.000  2015-01-01 03:00:00.000

And I have converted the startTime field from varchar to dateteime and called it sessionHour:

Convert(datetime, startTime) As sessionHour

Additionally, I was able to get the hour of the startTime by doing:

DateAdd(Minute, 60 * (DateDiff(Minute, 0, startTime) / 60), 0)  As hourOf

Beyond that, I am lost as to how to parse out the minutes per hour. Help very much appreciated. Thanks!

Aucun commentaire:

Enregistrer un commentaire