Aelestra07
asked on
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??
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??
ASKER
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
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
for Hours : DateDiff("n", StartTime, ClampTime)\60
for Minutes : DateDiff("n", StartTime, ClampTime)%60
for Minutes : DateDiff("n", StartTime, ClampTime)%60
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,Fie
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).
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,Fie
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).
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
+ ':' + right('00' + convert(varchar,datediff(s
+ ':' + right('00' + convert(varchar,datediff(s
declare @lastdate datetime
set @firstdate = '05/02/2011 08:23:20.000'
set @lastdate = getdate()
select right('00' + convert(varchar,datediff(s
+ ':' + right('00' + convert(varchar,datediff(s
+ ':' + right('00' + convert(varchar,datediff(s
dateadd("s",datediff("s",S
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.