Average Elapsed Time in HH:MM - time can span days
Posted on 2011-05-02
I'm struggling with adding an average of elapsed time between two datetime data types to my report in BIDS.
The data is coming from MS SQL 2005, report is created in SSRS/BIDS 2005 and is laid out something like this:
ID StartTime ClampTime
1 01/06/2011 00:51 01/07/2011 04:50
2 01/14/2011 07:45 01/15/2011 12:54
3 01/30/2011 00:02 01/30/2011 21:42
So average would need to show as... 26:16:however many seconds.
I finally got to the point of using DateDiff in minutes and dividing by 60 to get hours (DateDiff("n", StartTime, ClampTime)/60), but this is not the format we need or want (returning, for example, 26.27(hours) instead). Also found where I could just get the number of hours (26.00) with (DateDiff("n", StartTime, ClampTime)\60), but then I don't know how to get to the remainder this stripped away to try to return that in minutes...if that makes any sense.
I've also tried changing the format in the Properties window, but to no avail.
I'm sure I'm making this much harder than it is. What should I be doing instead??