Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to add the logic for contiguous months in the SP 1

Status
Not open for further replies.

cristi22

MIS
Aug 17, 2005
155
US
Hello all –

Please help, running out of time & have no clue what to do [3eyes]

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,
Cristi

 
Cristi,
I think that the easiest way to do this is to code and SQL statement at the beginning of the SP that is based around the same selection criteria in the main SQL. Instead of obtaining columns from the tables, code a COUNT where date less than variable date - 6 MONTHS. If the count is not 6, then error.

I've not explained this in great detail, but hope you can see what it is I'm getting at. If not, get back to me.

Marc
 
Marc, Thank you very much for your help!

The period is always 1st of the month, I don't think i have to verify year

If I'd do the following:

Code:
SELECT  count(period) FROM umass_contr
WHERE 
month( period) >= ( month  (DATE( '2005-07-01'))  - 6)     and year(period) =   year (DATE( '2005-07-01'))  
and month( period) <=  ( month  (DATE( '2005-07-01')) -1)  and year(period) =   year (DATE( '2005-07-01'))  
group by period

How do I add the code to look at bypass records where p_ctr < 6 ?

thanks again for your help!
Cristi
 
Cristi,
I was thinking something slightly easier, along the lines of:
Where period > '2005-07-01' - 6 MONTHS

You could then check the count and bypass the 2nd piece of SQL if not 6.

I'm afraid my knowledge of SP's is a little limited as all the SPs I've written have been in Cobol. Can you have IF statement logic in the SP you are coding?
 
thanks Marc!
i might have to create a temp table & do the join or use a subquery
 
I think I am on the right track, but.....
PLEASE HELP!!!!

Code:
  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  c3
        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')   )

and 6 = (
       select count(1) from umass_contr 
       where employer = c3.employer
       and period between (date('2005-07-01') - 6 month) and (date('2005-07-01') - 1 month)
    )
   GROUP BY
c3.employer,  period,
    (case app when   'F' then (case MARIT when 'M'  then 'STANDARD_FAMILY' else 'STANDARD_SINGLE' end) else ''end)
 
Cristi,
Only just seen this, and am not sure of your count(1) in the final subselect. Shouldn't that be count(*) ?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top