Determining Consecutive Days using Crystal Reports 10

[nolanma]

I am working on a report where I need to determine consecutive days worked using Crystal Reports 10. I am working with a time sheet date field and am having trouble coming up with a formula to do this. Can anyone help?

 

[synapsevampire]

Are you saying that you want to determine the number of days in a row for a specific date field?

You would likely need a formula to do this that iterates a counter, but you need to display meaningful technical information, this conveys very little.

Crystal version
Database/connectivity used
Example data
Expected output

-k

 

[nolanma]

Yes, I need to count the number of days in a row that a person worked. The report is to show personnel who have worked more than 7 consecutive days or more than 14 consecutive days without a day off. I am pulling the data from our time entry system and would use the time sheet date in creating a formula to do the calculation. I'm not sure what other technical information is needed....I am looking for tips on how to begin creating a formula to do the count. Thanks for your assistance.

 

[lbass]

You could try a formula like the following, after sorting by date:

whileprintingrecords;
numbervar cnt;

if {table.date} = previous({table.date}) + 1 then
cnt := cnt + 1 else
cnt := 1;

-LB

 

[synapsevampire]

Group by the person, and sort by the date.

In the Group Header place:

whileprintingrecords;
numbervar DayCnt:=0;
Booleanvar days7:=false;
Booleanvar days14:=false

In the details place:
whileprintingrecords;
numbervar DayCnt;
Booleanvar days7;
Booleanvar days14;
If {table.personid} = previous({table.personid})
and
{table.date} = previous({table.date}) then
DayCnt := DayCnt+1
else
DayCnt:=0;
If DayCnt >6 and DayCnt < 14 then
Days7:= True
else
If DayCnt >13 then
Days14:= True

In the group footer place:
whileprintingrecords;
Booleanvar days7:=false
Booleanvar days14:=false
"Days 7: " & Days7 & " - " & "Days 14: " & Days14

You didn't state how you wanted to display, but the above theory should get you there.

-k

 

[nolanma]

Thank you for both of the suggestions! I will try them and see what happens.