Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: time different in the same column Post Reply Post New Topic
Author Message
lalakeyorai
Newbie
Newbie


Joined: 01 Nov 2009
Online Status: Offline
Posts: 19
Quote lalakeyorai Replybullet Topic: time different in the same column
     Posted: 30 Nov 2009 at 1:18am

Hi,

from these series of data, how can i get the time different between all 'active' and 'clear' status? i want to display different time for both Unresponsive and Down.

<Time>                        <Event_Name>   <Event_Status>
6 Nov 2009  12:18PM    Unresponsive        Active
6 Nov 2009  12:20PM    Unresponsive        Clear
6 Nov 2009   2:52PM     Down                   Active
6 Nov 2009   2:55PM     Down                   Clear
6 Nov 2009   3:24PM     Unresponsive        Active
6 Nov 2009   3:30PM     Unresponsive        Clear
6 Nov 2009   4:18PM     Unresponsive        Active
6 Nov 2009   4:22PM     Unresponsive        Clear
 

i've tried to use 'datediff' function but my start and end time is under the same column. how can i write the correct syntax?
 
 
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 30 Nov 2009 at 6:29am
 you would want to use next() or previous() like:
datediff(hour,{table.field}, next({table.field})
IP IP Logged
lalakeyorai
Newbie
Newbie


Joined: 01 Nov 2009
Online Status: Offline
Posts: 19
Quote lalakeyorai Replybullet Posted: 30 Nov 2009 at 11:55pm
Lockwelle,
 
Thank you so much for your reply. its just a simple syntax and i couldnt find it. its working as what i want but unfortunately there is another issue come out. the issue is when two time (t1 and t2) are different by more than 1 hour, the minute appear wrongly.
 
let say:
t1=13:50:02
t2=11:57:30
 
by right i should get 1:52:32 for the different but it appear 1:112:32. for t1 and t2 that different within the same hour, i manage to get the correct time different. how to correct it?
 
here is my syntax:
 
down:=  if {Command.Event_Name}= 'Unresponsive' and {Command.Event_Status}='Clear' then {@Time};
diff:=  datediff("s",down,previous({@Time}));
hrs:=   fix(diff/3600)*(-1);  //*(-1) to get positive value.
min:=   fix(diff/60)*(-1);
sec:=   (diff mod 60)*(-1);
totext(hrs,"00") + ":" + totext(min,"00") + ":" + totext(sec,"00");
 
and is it possible to sum all of these different time to be placed at the group footer?


Edited by lalakeyorai - 01 Dec 2009 at 12:09am
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 01 Dec 2009 at 6:09am
you need to subtract the seconds that are used up in the hour...52 + 60 = 112 so something like:
min:=   fix((diff-(hrs * 3600))/60)*(-1);
 
if you want to get rid of the -1, reverse the dates in the datediff...I can never remember which comes first.
 
As for summing them, create a shared variable and increment it, and then parse it in the group footer, like:
shared numbervar totalDiff;
 
diff:=....
totalDiff := totalDiff + diff;
...
 
 
in the footer you would use the same logic as in the formula now, but you wouldn't calculate the diff, just use totalDiff.  Remember to reset it to 0 in the group header.
 
HTH
IP IP Logged
lalakeyorai
Newbie
Newbie


Joined: 01 Nov 2009
Online Status: Offline
Posts: 19
Quote lalakeyorai Replybullet Posted: 03 Dec 2009 at 1:17am
Hi Lockwelle,

I manage to fix the minute issue and very happy for that. Thank you so much. Thumbs%20Up

For the sum issue, i'm still trying to figure out how to sum all of the different time by refer your example. Still confusing right now. But i there are another issues:

1. Can i hide the 00:00:00 so the it will not display on my record? How to hide it?

2. If the first data of the group started with 'Active' followed by 'Clear', then it will be a problem on displaying the time different. But if the data started with 'Clear', it show negative number. The 'Clear' data appeared first was due to the date & time selection in my report. the 'Active' data maybe on the previous date or time which are not include during date & time selection.

my data after using the datediff:

                                                                                    <timediff>
6 Nov 2009  5:08:03 AM    Unresponsive        Clear
                                                                                     -71:-42:-52
6 Nov 2009  5:57:06 AM    Unresponsive        Active
                                                                                     00:00:00
6 Nov 2009  7:41:31 AM    Unresponsive        Clear
                                                                                     01:44:25
6 Nov 2009   7:45:32 AM     Down                   Active
                                                                                     00:00:00
6 Nov 2009   1:10:29 AM     Down                   Clear
                                                                                     05:24:57
6 Nov 2009   1:14:30 AM     Unresponsive        Active
                                                                                     00:00:00
6 Nov 2009   3:10:56 AM     Unresponsive        Clear
                                                                                    01:56:26

Thanks in advance.


Edited by lalakeyorai - 03 Dec 2009 at 1:19am
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 03 Dec 2009 at 6:13am

you can put a suppression formula on the report object (right click, format object (probably text), general tab suppress) something like:

datediff("s",down,previous({@Time})) = 0
 
for the negative, you could suppress it as well, since the calculation is incorrect, just change the above to <= 0.
 
HTH
IP IP Logged
lalakeyorai
Newbie
Newbie


Joined: 01 Nov 2009
Online Status: Offline
Posts: 19
Quote lalakeyorai Replybullet Posted: 07 Dec 2009 at 8:09am
Lockwelle...
 
Thank you so much for your help... really appreciate it.... Clap
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | BAEZL

© 2004-2026 Brian Bischof. All Rights Reserved.