Say I have a table Table1 with 3 cols
and if I am looking for max number of consecutive days that a consultant is available within a date range, is there any way to do so using a SELECT i.e. not having to cursor through.
sample data (ordered by consultant_id, date_value)
consultant_id date_value available_ind
1 1/1/04 Y
1 1/15/04 Y
1 2/2/04 Y
1 2/3/04 N
1 2/11/04 Y
1 3/1/04 Y
1 3/11/04 N
1 3/21/04 Y
1 3/22/04 Y
so the result should be that the consultant is available for max of 3 consecutive days.