Is There a Way To Combine These Queries?

[lrdave36]

Hey guys,

I am again stuck trying to figure out a way to merge count queries together in one master query. Here is my queries:


query 1

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

INNER JOIN DSNP.PR01_T_ANTY_PYMT B

ON      A.RECIP_SSN_NBR=B.RECIP_SSN_NBR


WHERE    A.BENEF_STAT_CD IN ('AC', 'DP')
AND      A.BENEF_SEQ_NBR = 1
AND      B.ANTY_PYMT_DT = '2010-05-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-05-01' )
GROUP BY A.AGTY_SYS_CD, A.BENEF_STAT_CD
ORDER BY 3 DESC

query 2

SELECT  AGTY_SYS_CD, 
        BENEF_STAT_CD, 
        count(recip_ssn_nbr) as "TOTAL" from          

DSNP.PR01_T_RECIP_SYS
   WHERE    RECIP_RETIR_DT <= '2010-05-01'
   AND      BENEF_STAT_CD IN ('AC', 'DP')
   AND      RECIP_TYPE_CD = '10'
   AND      BENEF_SEQ_NBR = 1
   GROUP BY AGTY_SYS_CD, benef_stat_cd

Seems like it should be simple to merge the two together, but the problem is this code:


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-05-01' )


can not be part of the same SELECT that calculates the total.

I did try making query 1 as a derived table and referencing TOTAL on the outside. This fails because I need the query grouped by AGTY_SYS_CD, and I end up with two different AGTY_SYS_CD fields with that method.

I'm really confused now. Can anyone help?

 

[Crowley16]

not sure this would work, but it *may* be worth giving it a shot, basically turn the 2nd query int a sub query in the first query, i.e.

select
blah
, (
select count(col) as total
from tbl1
where tbl1.col = MainTbl.col
)
from
blah

you don't need to include the group by in the sub query since you've got it in the main query already, and you're filtering the sub query with the where clause.

I repeat, I'm not sure if it'll work.

--------------------
Procrastinate Now!

 

[Crowley16]

p.s. performance won't be good...

--------------------
Procrastinate Now!