Grouping by time period

[andresp]

Hi all,

Can someone offer suggestions as to how to accomplish the following?

I have a table with 3 fields: date, truck, and driver, in this table I have the next data:

2002-02-01, truck1, driver1
2002-02-02, truck1, driver1
2002-02-03, truck1, driver1
2002-02-04, truck2, driver1
2002-02-05, truck2, driver1
2002-02-06, truck1, driver1
2002-02-07, truck1, driver1

I want to generate a report that shows what trucks each driver had, and the period of time that the driver had the truck. Please notice that if a driver use a truck in 2 different time periods it should appears as 2 different rows. The report should look like this:

driver1

truck1 2002-02-01 - 2002-02-03
truck2 2002-02-04 - 2002-02-05
truck1 2002-02-06 - 2002-02-07

I have been trying a couple of ways to do this, but so far I haven't been lucky. I really appreciate any help or advice on this regard.

Thanks in advance.

andres

 

[John43]

Try to group by driver then group by truck.

 

[IdoMillet]

Within Driver, sort all records by date.

Grab in a variable the first date in the group (do that in the group header). In each record (detail section)
show:

Truck#, date_in_Variable + "-" + date_in_this_row

SUPPRESS this detail section with an expression like:

Next({Truck#}) = {Truck#} AND Next({Driver}) = {Driver}

this ensures you show info only on the last
contiguous set of dates for the same truck
within each driver.

Reset the date_in_variable in each detail record
if the Previous({Truck#}) <> {Truck#}

(remember to also reset it in the Group Header for
the Driver.

Cheers,
- Ido ixm7@psu.edu

 

[andresp]

Hi Ido,

I did like you suggested and it did work. Thanks a lot.

andres

 

[IdoMillet]

Andres,

Glad it worked.

By the way, you can do all of this in SQL, returning all &quot;segments&quot; directly from the DBMS. However, this requires a rather complex SQL statement and, as an exception, the approach I proposed above actually should run faster than the SQL approach.

If you don't mind, could you e-mail me the report with saved data. I may want to use it as an example for my students.

Cheers,
- Ido ixm7@psu.edu