Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Use number of Consecutive days in Select Criteria Post Reply Post New Topic
Author Message
crystal.credo
Newbie
Newbie


Joined: 19 May 2009
Location: Canada
Online Status: Offline
Posts: 2
Quote crystal.credo Replybullet Topic: Use number of Consecutive days in Select Criteria
     Posted: 19 May 2009 at 6:57am

Greetings,

I have a report that shows absent employees.  What I am trying to do is further qualify that report to only show me their information if they have 10 or more consecutive days listed as absent.

So:
7/5/2005 Joe Smith Absent
7/6/2005 Joe Smith Absent
7/7/2005 Joe Smith Absent
Will show in report, but
7/5/2005 Joe Smith Absent
7/6/2005 Joe Smith Absent
7/8/2005 Joe Smith Absent
will not show because the days aren't consecutive.

Also:
it would be very nice if it would show this in the report:
7/7/2005 Joe Smith Absent
7/8/2005 Joe Smith Absent
7/11/2005 Joe Smith Absent
While not consecutive, it is if you take the weekend out.  I don't know if this is possible but if there was a way to build this logic in, that would be most useful!

Any more information needed, please let me know.

Thanks in advance!

 

IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 19 May 2009 at 7:12am
my thoughts would be check the dates using datediff, if it is 1, they are consecutive.  Since I just answered a post using Next and previous, I would give those a try, as this seems a perfect time to, though my first thought would have been to use a shared variable.  With the shared variable you can reset it at the time the group changes and check for that in your formula...1/2 dozen / 6  either way same thoughts.
 
as for the weekend, you can use a function to determine the day of week and then can account for the jump in dates...depends on what your business rules are.
 
Hope this gives some ideas of directions that you can take.
IP IP Logged
crystal.credo
Newbie
Newbie


Joined: 19 May 2009
Location: Canada
Online Status: Offline
Posts: 2
Quote crystal.credo Replybullet Posted: 19 May 2009 at 12:29pm
Thanks for your help lockwelle !  This is what I have so far,
 
Datediff("d", {@Date worked}, NEXT({@Date worked}))
 
however it doesnt work the way I want it to, I will need to incorporate infinite looping to add the days up with an end condition as "when the difference is greater than 1 then end/ reset" Eg: if an employee has been sick for 3 days in a row, count them as 3 and then reset.
 
Also, I will need to reset for each employee which is a group in my current report. I figured out a way to identify weekends - so thats all good.
 
If you have any thoughts..please reply !
 
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 19 May 2009 at 1:42pm
you shouldn't need infinite looping, set a shared variable and just add to it.  something like:
shared numbervar conseq;
if datediff("d", {}, next{}) = 1 then
 conseq := conseq +1
else
 conseq :=0
 
and when the group changes, set conseq:=0.  Now this is assuming that as soon as the gap is bigger than 1, you would want to add a check for the weekend, that the number of consecutive days would be reset.  I don't know what is wanted, but you could increment a counter at the same time to record the number of absences...since that is different from the number of days missed.
 
HTH
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