Results 1 to 3 of 3

091310, 16:19 #1Registered User
 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 = '20100901' 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 < '20100901' ) 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 ) = '20100901' GROUP BY A.AGTY_SYS_CD, A.BENEF_STAT_CD
Difference is in query 1, I want ppl whose first payment date is 09012010. With deceased, I want people whose last payment date is 09012010.
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 = '20100901' 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 < '20100901' ) 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 ) = '20100901' GROUP BY A.AGTY_SYS_CD, A.BENEF_STAT_CD

091310, 19:23 #2Registered User
 Join Date
 May 2009
 Posts
 508
Provided Answers: 1dvdaddict32, 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 ?.colname = c.colname ) )
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.

091410, 14:00 #3Registered User
 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) = '20100901' ) 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) = '20100901' ) ) GROUP BY A.AGTY_SYS_CD  , A.BENEF_STAT_CD ;
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) = '20100901' OR MAX(B.ANTY_PYMT_DT) = '20100901' ) B ON A.BENEF_STAT_CD IN ('AC', 'DP') AND min_pymt_dt = '20100901' OR A.BENEF_STAT_CD = 'DD' AND max_pymt_dt = '20100901' WHERE A.BENEF_SEQ_NBR = 1 GROUP BY A.AGTY_SYS_CD  , A.BENEF_STAT_CD ;