date range for every change in value

J

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
 
J

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.
 
J

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top