Hello all –

Please help, running out of time & have no clue what to doL
Any ideas are REALLY appreciated!!!!

I have an SP (DB2 9) & CR (9) front-end


My SP counts ssn based on the user's entered period (July 1st hard coded for test) group by employer, app & period

Code:
 results of the SP:
9990003   07/01/2005 HIGH_SINGLE 88888888 SFHS   S    
9990003   07/01/2005 HIGH_FAMILY 99999999 SFHF   M

I need to add the logic to verify if the member has six contiguous months(period) of contributions (u_amt)
from the period entered by the user.
For example, the report is run on July 1 I would look back for u_amt for the
months of June, May, April, March, February and January.


Code:
 Input data
EMPLOYER  PERIOD     APP    SSN       PERIOD     U_AMT      
--------- ---------- ------ --------- ---------- -----------
9990003   07/01/2002 F      999999999 07/01/2002       27.81
9990003   08/01/2002 F      999999999 08/01/2002       31.29
9990003   09/01/2002 F      999999999 09/01/2002       31.29
9990003   10/01/2002 F      999999999 10/01/2002       31.29
9990003   11/01/2002 F      999999999 11/01/2002       31.29
Here is my SP
How do I add the logic??

Code:
CREATE PROCEDURE BASYS.UMASS_ENROLL ( in period date )
    DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
    -- Declare cursor
    DECLARE cursor1 CURSOR WITH RETURN FOR

    -- App of F = Standard Coverage (should not have app in SFH) or nk
    SELECT   employer , period,
    (case app when  'F' then (case MARIT when 'M'  then 'STANDARD_FAMILY' else 'STANDARD_SINGLE' end) else '' end) plandesc,
    Count(distinct ssn) tot_cnt
    FROM umass_contr         WHERE
    --employer='9990002' and
    period = date('2005-07-01')
    and  APP IN ('F' )
    and ssn not in ( select  distinct ssn from umass_contr  where app in ('SFHS','SFHF')  and period = date('2005-07-01')   )
    and ssn not in ( select  distinct ssn from umass_contr  where nk_type='NK'  and period = date('2005-07-01')   )
    GROUP BY
    employer,  period,
    (case app when   'F' then (case MARIT when 'M'  then 'STANDARD_FAMILY' else 'STANDARD_SINGLE' end) else ''end)

union

    -- HIGH Coverage (SFHS, SFHF)
    SELECT   employer , period, (case app when 'SFHS' then 'HIGH_SINGLE' when 'SFHF' then 'HIGH_FAMILY'  else '' end) plandesc, Count(distinct ssn) tot_cnt
    FROM umass_contr
        WHERE
    --employer='9990002' and
    period = date('2005-07-01')
    and  APP IN ('SFHS', 'SFHF'  )
    GROUP BY
    employer,  period,
    (case app when 'SFHS' then 'HIGH_SINGLE'
    when 'SFHF' then 'HIGH_FAMILY' else '' end)

union

-- NKs
    select employer, period,'NK' as plandesc, count (distinct ssn) as tot_cnt
    FROM umass_contr
        WHERE
    --employer='9990002' and
     period = date('2005-07-01') AND NK_TYPE = 'NK'
    and  APP IN ( 'FLAT' )
    GROUP BY
    employer,  period ;
    -- Cursor left open for client application
    OPEN cursor1;

END P1
THANKS A LOT,
Ann