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

Posted on 2011-05-02
Last Modified: 2012-06-27
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??
Question by:Aelestra07
    LVL 22

    Expert Comment

    by:Nico Bontenbal
    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.

    Author Comment

    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 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
    LVL 21

    Expert Comment

    by:Alpesh Patel
    for Hours : DateDiff("n", StartTime, ClampTime)\60
    for Minutes : DateDiff("n", StartTime, ClampTime)%60
    LVL 22

    Expert Comment

    by:Nico Bontenbal
    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).


    Author Comment

    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.
    LVL 22

    Accepted Solution

    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

    LVL 22

    Assisted Solution

    by:Nico Bontenbal
    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.

    Author Closing Comment

    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! :)
    LVL 9

    Expert Comment

    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)

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
    Written by Valentino Vranken. Introduction: In a previous article ( I announced that I would writ…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now