Subtracting time 1

[robinsql]

Hey all,

I am writing a report which has datetime appearing in the details.
The first column in the details section has the datetime and the second column has the event type.
If the event type is 'Off', I would like the third column to display the difference in the times from the first column from when the Event Type was 'On'.
I am basically running a report on a machine and there are lots of different events associated with it. I need to show the time elapsed between turning the machine on and off.

Example:

Time Event Time Elapsed
18/09/2003 01:00:23 On
18/09/2003 01:05:34 xxx
18/09/2003 01:09:23 yyy
18/09/2003 01:13:43 zzz
18/09/2003 01:45:23 Off 45 mins

How do I get the 45 mins to appear?!
Any help very gratefully received as I'm very stuck!

Cheers,
Robin

 

[crystalreporting]

You could create a couple of formulas; One would be @Time_On (call it what you like) and it would contain an if statement i.e.

if {table.event} = "On" then {table.datetime}

Then create another formula called @Time_Elapsed - it would also create an if statement;

if {table.event} = "Off" then {table.datetime} - {@Time_On}

You could then supress the @Time_On formula.

Peter Shirley

http://www.linkcorp.com

 

[crystalreporting]

Whoops - I just re-read your sample data - the last formula should read:

if {Event.Event_Description} = "Off"
then
DateDiff("n",{Event.Event_Occured},Maximum({@Time_On}))

Peter Shirley

http://www.linkcorp.com

 

[crystalreporting]

..and both of these formulas need to go into the detail section.

Peter Shirley

http://www.linkcorp.com

 

[vidru]

Another solution with two formulas...

Formula 1: @OnTime

dateTimeVar  LastOnTime;
if {Table.EVENT} = 'on' then LastOnTime := {Table.TheTime};
LastOnTime;

Formula 2: @TimeElapsed

if {Table.EVENT} = 'off' then
datediff('n',{@OnTime},{Table.TheTime})

Both would go in the Details section. Suppress @OnTime, and suppress @TimeElapsed unless the event = 'off'.

Make sure you sort the records by your datetime field, of course, and this will work for any number of days' events.

-dave

 

[robinsql]

Thanks a mill,
Worked fine. I can't mark your post as helpful though as my machine is screwed. It keeps telling me the RPC server is unavailable.
Cheers,
Robin