Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: How to suppress in between records Post Reply Post New Topic
Author Message
andrewsimon
Newbie
Newbie
Avatar

Joined: 28 Jun 2011
Online Status: Offline
Posts: 26
Quote andrewsimon Replybullet Topic: How to suppress in between records
    Posted: 16 Jul 2011 at 8:41pm
I have a table which contains events from an access control system.
I want to show First In and Last out of the employee per day(working hour 8:00 to 5:00),in addition to this
i want last out and Last in  for the lunch break(in between 12:45 & 2:15) for that day.Other events should be suppressed in the report.
I'm facing a problem to design the report.Pls help me...
 
 
 
bbbbb
IP IP Logged
yggdrasil
Senior Member
Senior Member
Avatar

Joined: 19 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 150
Quote yggdrasil Replybullet Posted: 18 Jul 2011 at 3:54am

I think I'd identify four specific event datetimes in the day for each person as

minimum time after 8:00.
maximum time before 12:45
minimum time after 14:15
maximum time before 17:00
 
where the date portion of the datetime is that of Currentdate
and  then suppress records where the time does not match any of these.
 
IP IP Logged
andrewsimon
Newbie
Newbie
Avatar

Joined: 28 Jun 2011
Online Status: Offline
Posts: 26
Quote andrewsimon Replybullet Posted: 27 Jul 2011 at 1:04am
Thank you.This is not working for me..Once again i will explain to you the situation.
The Event table looks like this
EmpID     DEVID     EVENTTIME
25              1             2011-04-28 08:42:17.000
25              2            2011-04-28 10:42:18.000
25              1            2011-04-28 11:42:18.000
26               2           2011-04-28  12:50:00:000
26               1           2011-04-28  14:10:00:000
26                2          2011-04-28   16:10:00:000
DEVID=1 for IN Transaction and DEVID=2 for OutTransaction
Requirement:-
I have to group the report by Department,employee and Date.
I have to show the following information in the report.
1.FirstIn And LastOut  for the employee per day( In between 08:00 AM and 05:PM)
2.Total Time Duration(Lastout-FirstIn)
3.Lunch break(Last out and Last in inbetween 12:15 Pm and 2:15 Pm)
4.Total outside=Lunch break ,he has taken
5.Total inside office= Total time Duration-Total outside.
I achieved the FirstIn and Lastout by ,showing the first in onthe groupheader of  Date and Last out on the group footer.
and the detail section is suppressed.
The problem is that i cannot findout the lunch break time.The max and min functions is not applicable because the DEVID is related to
another Table READER
READER ID   READER DESC
1                     Main entry
2                     Main Exit
Anybody knows any solution to design this report
bbbbb
IP IP Logged
sharona
Senior Member
Senior Member
Avatar

Joined: 16 Oct 2008
Location: United States
Online Status: Offline
Posts: 255
Quote sharona Replybullet Posted: 27 Jul 2011 at 2:29am
1st create a group by
Department,employee and Date.
then create a group by datetime by second
this should pull all the dates in sequential order

then create a formulas(my numbers would be the same as yours above)
1-if {devid}=1 then minumum(datetime) FIRST IN
1- if{devid}=2 then maximum(datetime) FIRST OUT
2-DATEDIFF('n',FIRSTIN,FIRSTOUT)- THIS WILL GIVE YOU TIME DIFFERENCE FOR THE ABOVE in minutes(if you need hours etc search for datediff interval)
have to think about the others for a bit. that will help you get started


Edited by sharona - 27 Jul 2011 at 2:30am
sharona
IP IP Logged
andrewsimon
Newbie
Newbie
Avatar

Joined: 28 Jun 2011
Online Status: Offline
Posts: 26
Quote andrewsimon Replybullet Posted: 27 Jul 2011 at 2:49am
Thank u Sharona.. I have reached that much.
and I have done a formula for finding out the lunchout.and here it is:
Local DateTimeVar Time1 :={EVENTS.EVENT_TIME_UTC};
stringvar y := Cstr(datepart("YYYY",Time1,0));
 
Numbervar y1 := Tonumber(y);
stringvar m := Cstr(datepart("M",Time1,0));
Numbervar m1 := Tonumber(m);
stringvar d := Cstr(datepart("D",Time1,0));
Numbervar d1 := Tonumber(d);
DateTimevar PlannedTime1 := DateTime (y1,m1 ,d1 ,12 ,45 ,00 );
DateTimevar PlannedTime2 := DateTime (y1,m1 ,d1 ,14 ,15 ,00 );
if ( EVENTS.EVENT_TIME_UTC>= PlannedTime1 AND ({EVENTS.EVENT_TIME_UTC}<=PlannedTime2)and ({READER.TIMEATT}=2)
then
DateTimeVar out :=DateAdd("h",3,{EVENTS.EVENT_TIME_UTC});
but it is not doing the job,what i required.
 Dead


Edited by andrewsimon - 27 Jul 2011 at 2:52am
bbbbb
IP IP Logged
Post Reply Post New Topic
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



This page was generated in 0.031 seconds.