show consecutive date ranges 1

[YANKRAY]

CR10.0

I have a Crystal Report that pulls a record for each day an employee works. I am grouping all the employee records and further grouping the employees by Supervisor.

I am trying to show on the group2 Footer the maximum consecutive days the employee did not work in the period.

I can show the various breaks by subtracting the previous work date from the work date at the detail level, but cannot show in the group 2 footer the maximum # of days break in the period.

For example:

Day Worked
Employee one 9/1/2006
Employee one 9/2/2006
Employee one 9/4/2006
Employee one 9/8/2006
Employee one 9/9/2006
Employee one 9/10/2006

Group 2 footer
Employee one Days worked Max # of consecutive off
6 3

Any suggestions would be appreciated.
Ray
Where the days off from 9/4 through 9/8 would be the maximum break for the time period.

 

[IanWaterman]

YOu could try a formula like

@Non Working Days

WorkDateField - Previous(WorkDateField)-1

In you summary just do a maximum summary of that formula.

Previous is a crystal function , the first record will cause the formula to return a Null, but that should not have any effect on your results.

Ian

 

[lbass]

Use a variable to collect the maximum. Let's say your difference formula is {@gap}:

if previous({table.emplID}) = {table.emplID} then
datediff("d", previous({table.date}), {table.date})

Then use a formula like the following in the detail section:

whileprintingrecords;
numbervar maxgap;
if {@gap} > maxgap then
maxgap := {@gap};

In the group footer for employee, use:
whileprintingrecords;
numbervar maxgap;

In the group header for employee, use:
whileprintingrecords;
numbervar maxgap := 0;

-LB

 

[YANKRAY]

LB -

Perfect solution to my problem. Thanks!

Ray