Contiguous dates grouping and totaling

[Gavn]

Greetings all!

I have a business problem that I would like to solve using Crystal Reports 6.

My department needs to group a series of dates into contiguous blocks, example:

2003/01/01
2003/01/02
2003/01/03
2003/02/05
2003/03/12

In the above example, I need to group the days that happen together, and break them out as distinct blocks, so that a total of the above would yield:
3 distinct groups.

In addition to this, I need to somehow allow for weekends and consider any grouping that has a weekend break in it as a single group rather than two.

So that:

2003/03/12
2003/03/15

Would be considered a single group, assuming 13 and 14 are Saturday and Sunday.

Does anyone know if this is possible in Crystal Reports???

Thanks,

Gavin.

 

[kenhamady]

You can't make true CR Groups this way, but you might be able to accomplish the same things (like subtotals) without true groups.
It depends on what you want to do with the groupings once you have them. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[Gavn]

Thank you for replying, Ken!

I need to display total number of distinct groups by employee, by month specified in parameter. Example:

Emp A
2003/01/02
2003/01/03
2003/01/04

Emp B
2003/01/17
2003/01/20

Emp C
2003/01/23
2003/01/24
2003/01/28

Report would look (prefer) like:

Employee Days Incidents
-------- ---- ----------
Emp A 3 1
Emp B 2 2
Emp C 3 2

For month of: January

What do you think? Do-able?

Thanks,

Gavin.

 

[kenhamady]

I think you can, but it won't be simple.
1) Group by Employee, and then sort by date.
2) Write a formula that defines a contiguous dates - something like:

Date + 1 = Next(Date) or
if DayOfWeek(Date) = 6
then Date + 3 = next (Date)

Then you could write a running total with variables that increments the varialbe only when the date isn't contiguous. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[Gavn]

Thank you very much, Ken!

Gavin.