• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1689
  • Last Modified:

Average Elapsed Time in HH:MM - time can span days

Hi all,

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??
2 Solutions
Nico BontenbalCommented:
you could use datediff("s",Starttime, ClampTime) to get the seconds between the two dates. Then you could add these seconds to for example 1/1/2000. This returns a date. Something like:
dateadd("s",datediff("s",Starttime, ClampTime),dateserial(2000,1,1).
Don't know if I got the syntax right, but I don't have time to test is now. Then you can format the result as:
dd hh:mm:ss
This will give you the number of days up to the seconds. Only works if there is never more than 31 days. If this can be the case we need some more coding. If you need more information I can provide a working example later today.
Aelestra07Author Commented:
Thanks for the fast response, Nicobo! I don't guess I'm doing it correctly, so more help later in the day would be great!

The 31 day restriction won't be a problem...at max it's only ever up to 3-5 days. Preferrably, we would be able to keep those numbers between 24 and 36 hours, so we're using this to track as a performance measure and be sure we're staying within the time allotted. That's also why the users are wanting to see hh:mm:ss moreso than dd hh:mm:ss.

I entered the expression you posted (syntax works btw), but the result is something like 1/2/2000 3:59 AM.

Is there anything else I could post that would help to see what I'm doing/not doing?

Thanks again
Alpesh PatelAssistant ConsultantCommented:
for Hours : DateDiff("n", StartTime, ClampTime)\60
for Minutes : DateDiff("n", StartTime, ClampTime)%60
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Nico BontenbalCommented:
I thought by "time can span days" you meant the difference could span multiple days, but you mean the start an end could be on different day.

You have the difference as a date now. You can use the format to display it as you want. When you set the format propery of the text box to:
d "days" hh:mm:ss
You'll get '2 days 3:59:00' instead of '1/2/2000 3:59 AM'. Which is one day to many because the day can never be 0 in a date, but since you want hours not days you could use something like this in the expression:

=datediff("h", Fields!StartTime.Value,Fields!ClampTime.Value) & ":" & Format(dateadd("s",datediff("s",Fields!StartTime.Value, Fields!ClampTime.Value),dateserial(2000,1,1)), "mm:ss")

The datediff("h") part gives you the difference in hours (could be more than 24) and the Format(.. part gives you the minutes and seconds).

Aelestra07Author Commented:
Nicobo - that's it exactly for the formatting and the difference! There's just one more hurdle to it. This gives exactly what I need in comparing the difference between two dates, but it returns #Error when I try to get the average of all the date/times.
And, you're right, though it can span days and be 1/2 a day or 3 days or 5 days, the user needs to see all those days in hours.

PatelApesh - BIDS doesn't seem to accept % or modulo in any expression. It sees it as a syntax error and marks with the red hashmarks and won't compile.
Nico BontenbalCommented:
Yes sorry, I forgot this was the original question. We need to get a bit more clever for this. You can use this expression:

          dateadd("s",Avg(datediff("s", Fields!StartTime.Value,Fields!ClampTime.Value)),dateserial(2000,1,1))         
& ":" &
Format(dateadd("s",avg(datediff("s",Fields!StartTime.Value, Fields!ClampTime.Value)),dateserial(2000,1,1)), "mm:ss")

Open in new window

Nico BontenbalCommented:
Avg(datediff("s", Fields!StartTime.Value,Fields!ClampTime.Value)
part returns the average difference in seconds. We add that to dateserial(2000,1,1) to get a date value. And then use a datediff "h" to compare this to 01/01/2000 to get the number of hours. The minutes and seconds part is the same as for the rows, I only added the avg function.  There is probably an easier way to do this but I couldn't think of one.

You can use something like
=DateDiff("n", fields!StartTime.Value , Fields!ClampTime.value) mod 60
on the rows to get the minutes part of the difference. And
=Avg(DateDiff("n", fields!StartTime.Value , Fields!ClampTime.value)) mod 60
on the totals to get the minutes part. the \ will probably get you the hours. So you could combine these two if you only need to know the hours and the minutes.
Aelestra07Author Commented:
Thank you, thank you, thank you so much!! This is perfect and you made it extremely easy to understand - that's a big deal when you're learning! :)
declare @firstdate datetime
declare @lastdate datetime

set @firstdate = '05/02/2011 08:23:20.000'
set @lastdate = getdate()

select right('00' + convert(varchar,datediff(s,@firstdate,@lastdate)/3600),2 )
 + ':' + right('00' + convert(varchar,datediff(s,@firstdate,@lastdate)/60),2 )
   + ':' + right('00' + convert(varchar,datediff(s,@firstdate,@lastdate)% 60) ,2)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Tackle projects and never again get stuck behind a technical roadblock.
Join Now