Elasped Time 1

[ainkca]

I'm using Business Objects 5.1.2

Does anyone know how I can create and object that is the elapsed time between two Date/Time objects?

The objects(PCM_AUDIT.AUDIT_START_DT and PCM_AUDIT.AUDIT_END_DT)contain data that looks like this:
10/27/2003 23:28:06

Sometimes the elapsed time is hours... sometimes it's only a few seconds, and sometimes it could span the midnight hour.

Any ideas? I'm frustratingly out of my depth on this one, and I could really use some help.

Thanks,
ainkca

 

[blom0344]

It is quite a job getting it done with reporter functions, but it should be possible:

Step 1:

Get the date part from the object en use the days_between function to validate whether timestamps are on one day or not.

If they are on the same day then use variables with substring to get at the hour part, the minute part and the second part. Calculate this to 'seconds after midnight' like:

=ToNumber(SubStr(FormatDate(<object> ,&quot;HHmmss&quot ,1 ,2))*3600+ToNumber(SubStr(FormatDate(<object> ,&quot;HHmmss&quot ,3 ,2))*60+ToNumber(SubStr(FormatDate(<object> ,&quot;HHmmss&quot ,5 ,2))

Do this for both objects ,subtract the second after midnight values and you have the time difference in seconds.

Reformat like:

=FormatNumber(Truncate((<Time difference>/60) ,0) , &quot;00&quot + &quot;:&quot; + FormatNumber(Mod(<Time difference> ,60) ,&quot;00&quot

Now if the timestamps are on two different days , things are a bit more complicated.

You will have to calculate a 'seconds TO midnight' using :

86400 - 'seconds after midnight' and add that to 'seconds after midnight' of the later date.

I will gladly sent you an example report I made earlier that does these kind of calculations (on ORACLE data). Drop me a mail.
(I am also on 5.1.2)





T. Blom
Information analyst
tbl@shimano-eu.com

 

[ainkca]

Would I use the same syntax if I created an object in the universe instead of on the report? I did some 'playing', and this is what I came up with. It's not working of course, but I'm closer than I was. I'm going to try your suggestion.

Here's what I did:
trunc(nvl(PCM_AUDIT.AUDIT_END_DT,sysdate) - nvl( PCM_AUDIT.AUDIT_START_DT,sysdate),0) || ' ' ||
trunc((mod(nvl(PCM_AUDIT.AUDIT_END_DT,sysdate) - nvl( PCM_AUDIT.AUDIT_START_DT,sysdate),1))*24) || ':' ||
trunc(mod((((nvl(PCM_AUDIT.AUDIT_END_DT,sysdate) - nvl(PCM_AUDIT.AUDIT_START_DT,sysdate))*24)*60),60))

That gives me days, hours and minutes, like this:
1) 10/28/2003 14:30:06 10/28/2003 14:30:27 0 0:00
2) 10/28/2003 15:49:23 10/28/2003 15:54:50 0 0:5
#2 should have 27 seconds, but I can't seem to do it, and it should also show 0 0:05.

I'm going to drop it and try your suggestion, that looks better. Thanks so much for you help, I'll let you know how I make out.

ainkca

 

[blom0344]

For anyone else interested. This is a solution I mailed to Ainkca on ORACLE data with time and date coming in seperate fields. It takes into account that the later time is the next morning and it properly formats the resulting final difference as : '00:00:00'



Day_check = DaysBetween(<Date1> ,<Date2>)

seconds1 = ToNumber(SubStr(FormatDate(<Time1> ,&quot;HHmmss&quot ,1 ,2))*3600+ToNumber(SubStr(FormatDate(<Time1> ,&quot;HHmmss&quot ,3 ,2))*60+ToNumber(SubStr(FormatDate(<Time1> ,&quot;HHmmss&quot ,5 ,2))

seconds2 = ToNumber(SubStr(FormatDate(<Time2> ,&quot;HHmmss&quot ,1 ,2))*3600+ToNumber(SubStr(FormatDate(<Time2> ,&quot;HHmmss&quot ,3 ,2))*60+ToNumber(SubStr(FormatDate(<Time2> ,&quot;HHmmss&quot ,5 ,2))

Time diff = If (<Day_check>=0) Then <seconds2>-<seconds1> Else If (<Day_check> =1) Then (86400 -<seconds1>+<seconds2>) Else 0

Time diff format hour =FormatNumber(Truncate((<Time diff>/3600) ,0) , &quot;00&quot

Time diff format minute =FormatNumber(Truncate((<Time diff>-Truncate((<Time diff>/3600) ,0)*3600)/60 ,0) ,&quot;0&quot

Time diff format seconds =FormatNumber((<Time diff>)-ToNumber(<Time diff format hour>)*3600-ToNumber(<Time diff format minute>)*60 ,&quot;0&quot

Time diff final =If(Truncate((<Time diff>-Truncate((<Time diff>/3600) ,0)*3600)/60 ,0)<10) Then <Time diff format hour>&&quot;:&quot;&&quot;0&quot;&<Time diff format minute>&&quot;:&quot;&<Time Diff format seconds> Else <Time diff format hour>&&quot;:&quot;&<Time diff format minute>&&quot;:&quot;&<Time Diff format seconds>



T. Blom
Information analyst
tbl@shimano-eu.com

 

[ainkca]

Here's what finally worked. I created several variables on my report, detailed below:
(Probably this will only work for when the days between is 0 or 1, I only did it for when the start and end date were the same, or the start and end date spanned the midnight hour... my data won't ever be otherwise)

DaysBetween:
=DaysBetween(ToDate(FormatDate(<Audit Start Dt> ,&quot;mm/dd/yyyy&quot ,&quot;mm/dd/yyyy&quot , ToDate( FormatDate(<Audit End Dt> ,&quot;mm/dd/yyyy&quot ,&quot;mm/dd/yyyy&quot)

SecondsElapsedTime:
=ToNumber(SubStr(FormatDate(<Audit End Dt> ,&quot;HHmmss&quot ,1 ,2))*3600+ToNumber(SubStr(FormatDate(<Audit End Dt> ,&quot;HHmmss&quot ,3 ,2))*60+ToNumber(SubStr(FormatDate(<Audit End Dt> ,&quot;HHmmss&quot ,5 ,2))-(ToNumber(SubStr(FormatDate(<Audit Start Dt> ,&quot;HHmmss&quot ,1 ,2))*3600+ToNumber(SubStr(FormatDate(<Audit Start Dt> ,&quot;HHmmss&quot ,3 ,2))*60+ToNumber(SubStr(FormatDate(<Audit Start Dt> ,&quot;HHmmss&quot ,5 ,2)))

SecondsAfterMidnightStart:
=ToNumber(SubStr(FormatDate(<Audit Start Dt> ,&quot;HHmmss&quot ,1 ,2))*3600+ToNumber(SubStr(FormatDate(<Audit Start Dt> ,&quot;HHmmss&quot ,3 ,2))*60+ToNumber(SubStr(FormatDate(<Audit Start Dt> ,&quot;HHmmss&quot ,5 ,2))

SecondsAfterMidnightEnd:
=ToNumber(SubStr(FormatDate(<Audit End Dt> ,&quot;HHmmss&quot ,1 ,2))*3600+ToNumber(SubStr(FormatDate(<Audit End Dt> ,&quot;HHmmss&quot ,3 ,2))*60+ToNumber(SubStr(FormatDate(<Audit End Dt> ,&quot;HHmmss&quot ,5 ,2))

ElapsedTimeOverMidnight:
=86400-<SecondsAfterMidnightStart> + <SecondsAfterMidnightEnd>

Elapsed Time:
= If <DaysBetween> = 0 Then FormatNumber(Truncate((<SecondsElapsedTime>/3600) ,0) ,&quot;00&quot + &quot;:&quot; + FormatNumber(ToNumber(FormatNumber(Truncate((<SecondsElapsedTime>/60) ,0) ,&quot;00&quot - ToNumber(FormatNumber(Truncate((<SecondsElapsedTime>/3600) ,0) ,&quot;00&quot) *60) ,&quot;00&quot + &quot;:&quot; + FormatNumber(Mod(<SecondsElapsedTime> ,60) ,&quot;00&quot Else FormatNumber(Truncate((<ElapsedTimeOverMidnight>/3600) ,0) ,&quot;00&quot + &quot;:&quot; + FormatNumber(ToNumber(FormatNumber(Truncate((<ElapsedTimeOverMidnight>/60) ,0) ,&quot;00&quot - ToNumber(FormatNumber(Truncate((<ElapsedTimeOverMidnight>/3600) ,0) ,&quot;00&quot) *60) ,&quot;00&quot + &quot;:&quot; + FormatNumber(Mod(<ElapsedTimeOverMidnight> ,60) ,&quot;00&quot

Next hurlde will be to ADD UP the elpased time so I get a total for each 'section'. (By Nursing Station, by charting session, etc.).

Any ideas?

 

[blom0344]

ainkca,

Did you receive the example report I send you today?
It is quite close to the one you are describing, anyway if you worked out the solution by yourself so much the better...

T. Blom
Information analyst
tbl@shimano-eu.com

 

[blom0344]

Getting the formatted total for a section is the easy part:

Based on my example:

Time diff section = =Sum(<Time diff>) In <Section>

Where section can be your nursing station.

Now apply the batch of formatting formulae on the variable 'Time diff section' instead of on 'Time diff'

That should work equally well.

I would send you the example, but i'm not sure you need it any longer

T. Blom
Information analyst
tbl@shimano-eu.com