>Grouping the status doesn't work since there are other statuses between the 'grouped' status. eg
>
>suspend 01/01/2001
>resume 01/30/2001
>suspend 03/01/2001
>resume 03/30/2001
>
>We usuallly look at the time a consumer was suspended in a date range. For instance, consumer was suspended for 30 days in Jan or 60 days between Jan 1 and May 1, in the above example. Adding an end date field is looking more and more attractive. :)
>
>John
>
>>Will adding a group on Status work?
Then, group on the Beginning date --
GROUP BY 1,3,2
ORDER BY 1,3,2
(note the sequence). I think that will address what you need.
I did a road management app once with the same issue. I finally decided to add ending mileage.
There's no real good solution in the relational model because you have to break the rule of no inter record dependencies in one way or another.
Leaving out the ending value means that you do require another record to complete the information in the current record. It makes data entry easier and querying much more difficult.
Including the ending value means that each record has all the information appropriate. However, during data entry, other records must be modified. On the other hand, querying is much easier.
HTH,
Jay