How to add the logic for contiguous months in the SP
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
