summarize records by date range
tfaJun 11 2010 — edited Nov 26 2010I have a challenging project where I need to report a summary record for a range of dates for each ID. I'm not against using a plsql helper function to derive the summary result as I'm not sure it can all be done in sql. Any help would be appreciated as my attempts have failed.
Here is a sample of the data.
ID START_DATE END_DATE
445 01-JAN-2010 30-APR-2010 --example concurrent
445 01-JAN-2010 31-MAY-2010
445 17-MAY-2010 06-AUG-2010
2710 01-MAY-2010 31-AUG-2010 --example consecutive
2710 01-SEP-2010 31-DEC-2010
2710 01-JAN-2011 30-APR-2011
2710 01-MAY-2011 31-AUG-2011
658 01-JAN-2010 30-APR-2010 --example concurrent
658 01-JAN-2010 31-MAY-2010
658 01-JAN-2010 31-MAY-2010
108 28-DEC-2009 22-JAN-2010 --example non-consecutive
108 29-MAR-2010 11-APR-2010
108 01-MAY-2010 31-MAY-2010
2535 01-MAR-2010 14-MAR-2010 --example 2 consecutive and 1 non-consecutive combination
2535 15-MAR-2010 28-MAR-2010
2535 05-APR-2010 02-MAY-2010
999 01-MAR-2010 14-MAR-2010 --example 2 concurrent and 1 consecutive combination
999 01-MAR-2010 24-APR-2010
999 25-APR-2010 02-MAY-2010
Here is the summary result of what I would like returned for each ID
ID START_DATE END_DATE
445 01-JAN-2010 06-AUG-2010
2710 01-MAY-2010 31-AUG-2011
658 01-JAN-2010 31-MAY-2010
108 01-MAY-2010 31-MAY-2010
2535 05-APR-2010 02-MAY-2010
999 01-MAR-2010 02-MAY-2010
Thanks,
Todd