Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010
    Posts
    32

    Unanswered: Can't Figure Out Why This is Failing

    Hey guys,

    I am trying to combine two queries together so I'll have a single result set that can be exported to Excel.

    Here is query 1

    Code:
    SELECT   A.AGTY_SYS_CD, A.BENEF_STAT_CD, 
             
             
             sum(case When RIGHT (A.VOUCHER_ID_CD,1) = 'D' and A.recip_type_cd = '10' then 1 end)   AS "DISABILITY RETIREES",
             sum(case when a.recip_type_cd = '20'  then 1 end) as "DEPENDENTS/SURVIVORS",
             sum(case when a.recip_type_cd = '10' and RIGHT (A.VOUCHER_ID_CD,1) <> 'D' then 1 end) as "A&SRETIREES",
             sum(case when a.recip_type_cd = '30' then 1 end) as "BENEFICIARY",
             sum(case when a.recip_type_cd = '40' then 1 end) as "QDRO_RECIPIENT",
             sum(case when benef_stat_cd = 'DD' and recip_type_cd in ('30') then 1 end) as "DECEASED BENEFICIARIES",
             sum(case when benef_stat_cd = 'DD' then 1 end) as "DECEASED RETIREES"
             
    
    
    FROM     DSNP.PR01_T_RECIP_SYS A, 
             DSNP.PR01_T_ANTY_PYMT B 
    
    
    WHERE    A.RECIP_SSN_NBR=B.RECIP_SSN_NBR 
    AND      A.BENEF_STAT_CD IN ('AC', 'DP') 
    AND      A.BENEF_SEQ_NBR = 1 
    AND      B.ANTY_PYMT_DT = '2010-09-01'
    AND NOT EXISTS ( SELECT * FROM DSNP.PR01_T_ANTY_PYMT B                            
                       WHERE                                                            
                        A.RECIP_SSN_NBR = B.RECIP_SSN_NBR AND                            
                        A.BENEF_SEQ_NBR = B.BENEF_SEQ_NBR AND                            
                        ANTY_PYMT_DT < '2010-09-01' )  
                        
    GROUP BY A.AGTY_SYS_CD, A.BENEF_STAT_CD

    Unfortunately, I discovered this query won't work for deceased people because it needs to be based on a completely different criteria.

    Here is the query to get my correct deceased totals:


    Code:
    select * from 
    
    FROM     DSNP.PR01_T_RECIP_SYS A, 
             DSNP.PR01_T_ANTY_PYMT B 
    
    
    WHERE    A.RECIP_SSN_NBR=B.RECIP_SSN_NBR 
    AND      A.BENEF_STAT_CD IN ('DD') 
    AND      A.BENEF_SEQ_NBR = 1 
    AND      (SELECT MAX(ANTY_PYMT_DT) FROM DSNP.PR01_T_ANTY_PYMT Z WHERE Z.RECIP_SSN_NBR = A.RECIP_SSN_NBR
         AND Z.BENEF_SEQ_NBR = A.BENEF_SEQ_NBR ) = '2010-09-01'
    
    GROUP BY  A.AGTY_SYS_CD, A.BENEF_STAT_CD

    Difference is in query 1, I want ppl whose first payment date is 09-01-2010. With deceased, I want people whose last payment date is 09-01-2010.


    How can I combine the two? I came up with the idea of doing a UNION, and putting a bogus code of 88 in the 2nd half so only deceased gets counted in the 2nd half.

    Unfortunately, the query pulls all my deceased in the table, not ones with just the max criteria. Can someone tell me where I'm going wrong with the logic???


    Code:
    
    SELECT   A.AGTY_SYS_CD, A.BENEF_STAT_CD, 
             
             
             sum(case When RIGHT (A.VOUCHER_ID_CD,1) = 'D' and A.recip_type_cd = '10' then 1 end)   AS "DISABILITY RETIREES",
             sum(case when a.recip_type_cd = '20'  then 1 end) as "DEPENDENTS/SURVIVORS",
             sum(case when a.recip_type_cd = '10' and RIGHT (A.VOUCHER_ID_CD,1) <> 'D' then 1 end) as "A&SRETIREES",
             sum(case when a.recip_type_cd = '30' then 1 end) as "BENEFICIARY",
             sum(case when a.recip_type_cd = '40' then 1 end) as "QDRO_RECIPIENT",
             sum(case when benef_stat_cd = 'DD' and recip_type_cd in ('30') then 1 end) as "DECEASED BENEFICIARIES",
             sum(case when benef_stat_cd = 'DD' then 1 end) as "DECEASED RETIREES"
             
    
    
    FROM     DSNP.PR01_T_RECIP_SYS A, 
             DSNP.PR01_T_ANTY_PYMT B 
    
    
    WHERE    A.RECIP_SSN_NBR=B.RECIP_SSN_NBR 
    AND      A.BENEF_STAT_CD IN ('AC', 'DP') 
    AND      A.BENEF_SEQ_NBR = 1 
    AND      B.ANTY_PYMT_DT = '2010-09-01'
    AND NOT EXISTS ( SELECT * FROM DSNP.PR01_T_ANTY_PYMT B                            
                       WHERE                                                            
                        A.RECIP_SSN_NBR = B.RECIP_SSN_NBR AND                            
                        A.BENEF_SEQ_NBR = B.BENEF_SEQ_NBR AND                            
                        ANTY_PYMT_DT < '2010-09-01' )  
                        
    GROUP BY A.AGTY_SYS_CD, A.BENEF_STAT_CD 
    
    
    
    
    UNION ALL
    
    SELECT   A.AGTY_SYS_CD, A.BENEF_STAT_CD, 
             
             
             sum(case When RIGHT (A.VOUCHER_ID_CD,1) = 'D' and A.recip_type_cd = '88' then 1 end)   AS "DISABILITY RETIREES",
             sum(case when a.recip_type_cd = '88'  then 1 end) as "DEPENDENTS/SURVIVORS",
             sum(case when a.recip_type_cd = '88' and RIGHT (A.VOUCHER_ID_CD,1) <> 'D' then 1 end) as "A&SRETIREES",
             sum(case when a.recip_type_cd = '88' then 1 end) as "BENEFICIARY",
             sum(case when a.recip_type_cd = '88' then 1 end) as "QDRO_RECIPIENT",
             sum(case when benef_stat_cd = 'DD' and recip_type_cd in ('30') then 1 end) as "DECEASED BENEFICIARIES",
             sum(case when benef_stat_cd = 'DD' then 1 end) as "DECEASED RETIREES"
             
    
    
    
    
    WHERE    A.RECIP_SSN_NBR=B.RECIP_SSN_NBR 
    AND      A.BENEF_STAT_CD IN ('DD') 
    AND      A.BENEF_SEQ_NBR = 1 
    AND      (SELECT MAX(ANTY_PYMT_DT) FROM DSNP.PR01_T_ANTY_PYMT Z WHERE Z.RECIP_SSN_NBR = A.RECIP_SSN_NBR
         AND Z.BENEF_SEQ_NBR = A.BENEF_SEQ_NBR ) = '2010-09-01'
    
    GROUP BY  A.AGTY_SYS_CD, A.BENEF_STAT_CD

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    dvdaddict32, Without knowing your tables or data, I am not sure if I followed everything you need.

    Here is a shot at what may work (needing you to flesh it out) using the first query you posted with these modifications to the WHERE:
    Code:
      AND A.BENEF_STAT_CD IN ('AC', 'DP', 'DD') 
      AND (A.BENEF_SEQ_NBR = 1
           OR A.BENEF_SEQ_nbr = (select MAX(BEFEF_SEQ_NBR
                                 FROM table? C
                                 where  ?.col-name = c.col-name
                                )
          )
    Including 'DD' in the BENEF_STAT_C list will get Deceased (I think).
    I think you imply that a BENEF_SEQ_NBR indicates the sequence benefits are paid. The First payment has a value of 1. I assume the Last payment would be equal to the MAX sequence number (for the unique 'unit' associated with it). You will need to supply the table name and the column(s) to correlate the subquery with.

    I THINK this will include the original list and all the deceased payments.

    You should then be able to include BENEF_SEQ_NUM in the CASE expressions to filter the values correctly. 1 is not deceased and >1 is deceased (again this is a guess).

    Hopefully this will either work or give you some ideas.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I guessed that the requirement is to count number of rows of DSNP.PR01_T_RECIP_SYS A which meets some conditions.

    If so, these would work.

    Example 1:
    Code:
    SELECT A.AGTY_SYS_CD
    --     , A.BENEF_STAT_CD
         , sum(case
               When A.BENEF_STAT_CD IN ('AC', 'DP')
                AND RIGHT (A.VOUCHER_ID_CD,1) = 'D'
                and A.recip_type_cd = '10' then
                    1
               end)   AS "DISABILITY RETIREES"
         , sum(case
               when A.BENEF_STAT_CD IN ('AC', 'DP')
                AND a.recip_type_cd = '20' then
                    1
               end)   as "DEPENDENTS/SURVIVORS"
         , sum(case
               when A.BENEF_STAT_CD IN ('AC', 'DP')
                AND RIGHT (A.VOUCHER_ID_CD,1) <> 'D'
                and a.recip_type_cd = '10' then
                    1
               end)   as "A&SRETIREES"
         , sum(case
               when A.BENEF_STAT_CD IN ('AC', 'DP')
                AND a.recip_type_cd = '30' then
                    1
               end)   as "BENEFICIARY"
         , sum(case
               when A.BENEF_STAT_CD IN ('AC', 'DP')
                AND a.recip_type_cd = '40' then
                    1
               end) as "QDRO_RECIPIENT"
         , sum(case
               when benef_stat_cd = 'DD'
                and recip_type_cd = '30' then
                    1
               end) as "DECEASED BENEFICIARIES"
         , sum(case
               when benef_stat_cd = 'DD' then
             1 end) as "DECEASED RETIREES"
     FROM  DSNP.PR01_T_RECIP_SYS A
     WHERE A.BENEF_SEQ_NBR = 1
       AND
       (   A.BENEF_STAT_CD IN ('AC', 'DP')
           AND
           EXISTS
           (SELECT 0
             FROM  DSNP.PR01_T_ANTY_PYMT B
             WHERE B.RECIP_SSN_NBR = A.RECIP_SSN_NBR
               AND B.BENEF_SEQ_NBR = 1
             HAVING
                   MIN(B.ANTY_PYMT_DT) = '2010-09-01'
           )
        OR
           A.BENEF_STAT_CD = 'DD'
           AND
           EXISTS
           (SELECT 0
             FROM  DSNP.PR01_T_ANTY_PYMT B
             WHERE B.RECIP_SSN_NBR = A.RECIP_SSN_NBR
               AND B.BENEF_SEQ_NBR = 1
             HAVING
                   MAX(B.ANTY_PYMT_DT) = '2010-09-01'
           )
       )
     GROUP BY
           A.AGTY_SYS_CD
    --     , A.BENEF_STAT_CD
    ;
    or

    Example 2:
    Logically, having clause in subquery is not neccesary.
    But, it may be effective for perofrmance.
    In other words, if optimizer of DB2 was clever enough, it wouldn't be necessary.
    Code:
    SELECT A.AGTY_SYS_CD
    --     , A.BENEF_STAT_CD
         , sum(case
               When A.BENEF_STAT_CD IN ('AC', 'DP')
                AND RIGHT (A.VOUCHER_ID_CD,1) = 'D'
                and A.recip_type_cd = '10' then
                    1
               end)   AS "DISABILITY RETIREES"
    .....
    .....
     FROM  DSNP.PR01_T_RECIP_SYS A
     INNER JOIN
           LATERAL
           (SELECT MIN(B.ANTY_PYMT_DT) AS min_pymt_dt
                 , MAX(B.ANTY_PYMT_DT) AS max_pymt_dt 
             FROM  DSNP.PR01_T_ANTY_PYMT B
             WHERE B.RECIP_SSN_NBR = A.RECIP_SSN_NBR
               AND B.BENEF_SEQ_NBR = 1
             HAVING
                   MIN(B.ANTY_PYMT_DT) = '2010-09-01'
               OR  MAX(B.ANTY_PYMT_DT) = '2010-09-01'
           ) B
       ON
           A.BENEF_STAT_CD IN ('AC', 'DP')
       AND min_pymt_dt  = '2010-09-01'
       OR
           A.BENEF_STAT_CD = 'DD'
       AND max_pymt_dt  = '2010-09-01'
     WHERE A.BENEF_SEQ_NBR = 1
     GROUP BY
           A.AGTY_SYS_CD
    --     , A.BENEF_STAT_CD
    ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •