date range for every change in value

[joemeshuggah]

anyone know if there is a way using sql to return min effective date and max
end date for every change in a value in another column.

for example
effective_date end_date indicator
01-01-08 01-15-08 n
01-16-08 01-28-08 y
01-29-08 02-05-08 n
02-06-08 02-08-08 n
02-09-08 02-11-08 y
02-12-08 02-20-08 y

looking to get a result set that would show as

min(effective_date) max(end_date) indicator
01-01-08 01-15-08 n
01-16-08 01-28-08 y
01-29-08 02-08-08 n
02-09-08 02-20-08 y

 

[jversiz via AccessMonster.com]

You could either format the date to show just the start month and end month,
then create a query to group by those two fields, or you could just group by
the indicator in your query and set the date fields to min and max.

Regards,
James C.

 

[joemeshuggah]

if i group by indicator and set the date fields to max and min i get the
following result:

the issue with the above is that there is an overlap in date ranges.