Summarize Policy effective to cancellation or Expiration Dates
===

I'm trying to calculate ranges a policy was in effect (in oracle10g).

I have data kinda like this:

`ID|POLICY|EFFDATE     |EXPDATE    |Status    |TRANEFFEDATE`

`10	1  01/01/2011* 01/01/2012  New	     01/01/2011`

`20	1  01/01/2011 01/01/2012  Endosement 01/31/2011`

`30	1  01/01/2011 01/01/2012  Canceled   02/10/2011*`

`40	1  01/01/2011 01/01/2012  Reinstated 02/15/2011*`

`50	1  01/01/2012 01/01/2013*  Renewal    01/01/2012`

`60	2  06/01/2011* 06/01/2012  New	     06/01/2011`

`70	2  06/01/2012 06/01/2013*  Renewal    06/01/2012`


	

I'd like to summarize the data to  (I *'d the dates I want to use above):

`POLICY|STARTDATE|ENDDATE|`

`1	01/01/2011	02/10/2011	--policy was cancelled on the traneffective date` 

`1	02/15/2011	01/01/2013	-- the policy `

`2	06/01/2011	06/01/2013	-- earliest effective date to the latest expiration date`

So that the earliest effective date and the latest expiration date are typically used unless there is a cancellation (where the transaction effective date is used) or a reinstatement (where the transaction effective date is also used).

The problem I'm having is figuring out how to account for multiple cancellations,reinstatements, and endorsements

Many thanks!

