Group consecutive dates

[cursedkorok]

Hi everyone,

I'm trying to create a report which looks at dated records and displays them as ranges then counts the individual ranges and the number of records. Report is grouped by ID. Selection is based on date ranges and record type.

Data example:

ID: Dated:
21 01/12/2023
No record as weekend
No record as weekend
21 04/12/2023
21 05/12/2023
21 06/12/2023
21 07/12/2023
21 08/12/2023
21 19/12/2023
21 20/12/2023
21 21/12/2023
21 22/12/2023
21 23/12/2023

Result I hope to achieve:

21

01/12/2023 - 08/12/2023 6 days
19/12/2023 - 23/12/2023 5 days

2 Instances​

I have it setup so it shows the ranges for working days that have records in the database but unsure how to include weekends in the range. i.e. 01/08/23 - 31/08/23 shows as 5 separate ranges because weekends break it apart and I want it to show as 01/08/23 - 31/08/23, 23 days, 1 instance unless one of the working days does not have a record in which case it would break into two instances etc.

Hope this resembles something that makes sense and that someone could help out please?

 

[SkipVought]

19/12/2023 - 23/12/2023 5 days
...is Tue-Sat

Are you including weekend dsys?

BTW, in every company I've worked for, IT has a Work Day Calendar that includes appropriately marked weekends and holidays as well as accounting periods. Your request would be a pretty simple query joining a Work Day Calendar table.

And if you can't or don't have one, then make one. It would only you a few minutes in a spreadsheet.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[cursedkorok]

Sorry the 23/12 is supposed to show as 19/12/2023 - 23/12/2023, but no the count should be 4, my mistake.

 

[SkipVought]

Check my previous post for some further tips.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[cursedkorok]

Sorry I'm not sure how a work day calendar helps in this case, I'm trying to achieve something similar to this, the report pulls absence details from a database I just need it to display as from - to if consecutive days and then count the number of instances.

 

[SkipVought]

Here's what a Workday Calendar table might look like. If it were me, i'd try to find your corporate Workday Calendar table.

At a minimum every day of the year and years following, needs to be present (and for as many years in the past as necessary).

Every corporate holiday and weekends need to be identified a no workdays, in order that the WDAY (workday) sequence is correct.

I started my sample Workday Calendar Table in November to demonstrate that the actual WDAY starting value is not critical.

I did my work in Excel, so the method of generating a table in your environment may be different. I can either show you my formulas or upload this sample Excel Workbook.

This is your December data joined to the Workday Calendar table to get WDAY, from which the Group can be derived. As you can see, a workday is missing, resulting in 2 groups. Notice the break in WDAY.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[pmax9999]

This can be done in Crystal using Variables and the Next() &/or Previous() functions. It will be quite time consuming to develop the formula but I would be prepared to assist.

Firstly I have a couple of questions:

[ol 1]
[li]In your sample data you have recorded 'No record as weekend'. I assume that is for demonstration purpoeses only and that weekends simply have nothing. Is that right? [/li]
[li]Using what you have provided, weekends could be be taken into account but public holidays will be ignored unless you have a Calendar table as explained by Skip. This means that what are really consecutive business days would be returned as separate date ranges. Would that be OK?[/li]
[li]If you do have a Calendar table that record public holidays, please provide some sample data so I can see the format of that table.[/li]
[/ol]

Regards
Pete

 

[pmax9999]

It looked like an interesting challenge, so I thought I would have a go at it.

Assuming you do not care about Public Hoildays and that Weekends simply have no data I did it this way:

1. Create the following Formula and place it in the Details Section:
[Code {@Calculate_Result}]
WHILEPRINTINGRECORDS;

Global StringVar RESULT;
Global NumberVar INST;
Global NumberVar DAYS;


IF ONFIRSTRECORD
THEN (
RESULT := ToText({Table.DATED}, 'dd/MM/yyyy') + ' to ';
INST := INST + 1;
DAYS := 1
)
ELSE
IF NOT ONLASTRECORD and
DayOfWeek({Table.DATED}, crMonday) in [2 to 5] and
{Table.DATED} = Previous({Table.DATED}) + 1 and
{Table.DATED} = Next({Table.DATED}) - 1
THEN (
RESULT := RESULT;
INST := INST;
DAYS := DAYS + 1
)
ELSE
IF NOT ONLASTRECORD and
DayOfWeek({Table.DATED}, crMonday) = 1 and
{Table.DATED} = Previous({Table.DATED}) + 3 and
{Table.DATED} = Next({Table.DATED}) - 1
THEN (
RESULT := RESULT;
INST := INST;
DAYS := DAYS + 1
)
ELSE
IF NOT ONLASTRECORD and
DayOfWeek({Table.DATED}, crMonday) in [2 to 5] and
{Table.DATED} = Previous({Table.DATED}) + 1 and
{Table.DATED} <> Next({Table.DATED}) - 1
THEN (
DAYS := DAYS + 1;
RESULT := RESULT + ToText({Table.DATED}, 'dd/MM/yyyy') + ' ' + ToText(Days, '#') + ' days';
INST := INST
)
ELSE
IF NOT ONLASTRECORD and
DayOfWeek({Table.DATED}, crMonday) in [2 to 5] and
{Table.DATED} <> Previous({Table.DATED}) + 1 and
{Table.DATED} = Next({Table.DATED}) - 1
THEN (
RESULT := RESULT + CHR(10) + ToText({Table.DATED}, 'dd/MM/yyyy') + ' to ';
INST := INST + 1;
DAYS := 1
)
ELSE IF ONLASTRECORD
THEN (
DAYS := DAYS + 1;
RESULT := RESULT + ToText({Table.DATED}, 'dd/MM/yyyy') + ' ' + ToText(Days, '#') + ' days';
INST := INST;
);

''
[/Code]

2. Create the Following formula and place it in the Group Footer Section:
[Code {@Display_Result}]
WHILEPRINTINGRECORDS;

Global StringVar RESULT;
Global NumberVar INST;

RESULT := RESULT + CHR(10) + ToText(INST, '#') + ' Instances'
[/Code]

Replace my field {Table.DATED} with the correct column name from your data source.

Hope this helps.

Regards
Pete.

 

[pmax9999]

I should also add that you will need to format the final formula to "Can Grow" as it will require multiple lines to display the result.

 

[pmax9999]

While I remain happy to help, I do expect that OP will at least acknowledge the attempt and indicate whether it worked or not.

I really am beginning to understand why there are so few of us here still trying to assist.

 

[cursedkorok]

Hi Pete,

I've only had a chance to test the formulas today, I'm really sorry for not getting back to you earlier and I genuinely appreciate you going through the effort and trying to help.

Please see below for results I got so far, this report runs through the whole employee database, I think the main issue might be that it does not reset after each employee/group which keeps adding to the .

The first employee had 1 days absence and this is the result:

Other than printing "to" when it's a single day rather than range, no problem.


Second employee was off for the whole month of August(broken up by the bank holiday on 28/08 which is expected):

It prints the previous employees sickness first and then adds the 2 instances of sickness 19 days before 28/08 and the 3 after.
Then shows 3 instances total (if it resets after each employee this would show 2 which is what we would want/expect to see.)

By the time we get to the last employee they have a list of all other sicknesses in the given date range + theirs an 20 odd instances.

Could you point me in the right direction on how to add the reset after function? The table referencing the employee is {Employees.Clock no} and the dates table is {Attendance_records.Dated}, also how can I fix up the formatting so the range is 01/08/2023 to 25/08/2023 rather than the result below?

More than happy to send a thank you contribution via buy me a coffee or something as it's definitely a massive help!

 

[cursedkorok]

I've moved to ' to ' concat to be before the end date range rather than at the end of the start date so it fixes the to being printed on a single date:

Not sure how to add "day(s)" at the end of that, if its the first record and the last record then print "DATE x day(s)

 

[pmax9999]

OK, try this:WHILEPRINTINGRECORDS;

Add the following RESET formula to the Group Header:

[Code {@VAR_RESET}]
WHILEPRINTINGRECORDS;
Global StringVar RESULT := '';
Global NumberVar INST := 1;
''
[/CODE]

Change the DISPLAY formula in the Group Footer to:
[Code {@}Display_Result]
Global StringVar RESULT;
Global NumberVar INST;

RESULT := RESULT + CHR(10) + ToText(INST, '#') + ' Instances' ;

IF RESULT LIKE '* to '
THEN RESULT := REPLACE(RESULT, ' to ', '')
ELSE RESULT := RESULT
[/Code]

This getting quite difficult to test as I only have a limited data set (created in a spreadsheet file using the data in your original post).

If there are still problems it would be helpful if you could provide a spreadsheet file containing the data where you see the problem (it can be limited to just the 2 columns of data as per your original post but otherwise I have to invest considerable time trying to dummy up data so I can get the same results as you, so I can then fix the code.

The alternative would be to upload the Crystal Report files (de-identified if necessary) with Saved Data.

Regards
Pete.