Merging Dates

[bongmarley]

I am creating a reportthat keep track of worker attendence.
I retrieve my data from an absence table. In the data base if a worker misses three days in a row then three seperate records are put into the table. When I create the report I want to be able to write toshow absence like this.

FROM TO DAYS
01/01/03 01/03/03 3

as of right now I can onlyget it toshow like this

FROM TO DAYS
01/01/03 01/01/03 1
01/02/03 01/02/03 1
01/03/03 01/03/03 1

When I loop through the records I want tobe able to merge more then one recordif its consecutive days. is this possible

 

[alistairpaul]

Without going into huge detail and writing the entire thing - here's what I would do.

Put all of it into a stored procedure, or just do it all through code.

Grab current record's date, and number of absence (1).
Goto next record.
If next/current record's date is same as variable holding last record's date, then increment the number of absence by 1.
Goto next record, repeat.
If next/current record's date is different from last date, and there's no absence, then keep track of the begin absence and end absence dates, spit them out and start tracking the new "start absence" date.

You could either have it handled within a stored procedure that would spit the entire thing back to you in .asp, or you could use vbscript/asp to parse out a standard SELECT recordset.