Working through very poor db design.

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. Received on Mon Jun 25 2007 - 14:18:09 CDT