Can't Figure Out Why This is Failing

dvdaddict32 · #1 (**permalink**) 09-13-10, 16:19

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

Stealth_DBA · #2 (**permalink**) 09-13-10, 19:23

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.

tonkuma · #3 (**permalink**) 09-14-10, 14:00

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
;

Internet Services

Web Development

Digital Marketing

Consumer Tech