Hello. Oracle plsql noob here with a challenging problem.. likely
because of a bad existing db design.

Say I have two tables.

Activity:
ssn
status
plan (but only for the most recent row.. terrible I know)
effective_date

and

Participation:
ssn
plan
effective_date
term_date



SSNs can have multiple Activity rows and mulitple Participation rows.
In some cases there is no Participation row for an SSN (again
terrible).

While not efforable, we can presume that there are no activity or plan
participation dates overlaping.

An SSN can have a change in status (resulting in a new activity row)
without a change in Participation, and visa versa.

An SSN can have changes in Activity during a period resulting in being
active twice during a period

I need to select Activity with it's correct plan for date period.

Selection of the correct Participation Plan will be based on dates,
and there may be more than one plan participation for a given SSN and
date range. It will not be good enough to select the most current
participation.

So given this sample data:

activity:
111, , active, 01-JAN-2007
111, , terminated, 15-FEB-2007
111, , active, 01-FEB-2007
111,planb, terminated, 01-Apr-2007
222,plana, terminated, 15-Apr-2007
222, ,active,15-Mar-2007
333,pland,active,01-Feb-2007
444,planc,terminated,15-Mar-2007
444, ,active,15-Mar-2007
444, ,active,20-Mar-2007


participation
111,plana,01-Oct-2006,01-Dec-2006
111,planb,01-feb-2007,01-Dec-2010
222,plana,01-Jan-2007,01-Dec-2010
444,planc,01-Jan-2007,2-Mar-2010

A process is passed start = Mar 1 2007 end = Mar 31 2007

would produce
111, planb, 01-Mar-2007, 15-Feb-2007 (note start = period start)
222, plana,15-Mar,31-March-2007 (note end = period end)
333, pland, 01-Mar-2007,31- March-2007
444, planc, 15-Mar-2007, 20-Mar-2007 (note end = start of new)
444, planc, 20-Mar-2007, 31-Mar-2007 (note end - period end)



basically (and it's nasty I know because of poor db design and
somebody should be shot) but there are several factors determine dates
on the extract.


* the start and end of related activity. You can't be active in two
plans at the same time.
* the period start and period end
* the identified plans start and end dates. This is a tough because
the the selected plan can only be identified by plans that are
avaiable during the activity in queist. like activity, there should
not be two plan participations that overlap.


We attempted to write a view to handle this, but what it was missing
was logic to (tough in a join) to set termination dates based on
subsiquent activity.