Please Help Me Combine These Queries

[lrdave36]

Hi guys,

I still can't seem to grasp the concept of combining queries together. UNION doesn't work in this case. I have these queries:


query 1

SELECT   AGTY_SYS_CD, count(*)  AS "TOTAL RETIREES" 
FROM     DSNP.PR01_T_RECIP_SYS 
WHERE    RECIP_RETIR_DT <= '2010-02-01' 
AND      BENEF_STAT_CD IN ('AC', 'DP') 
AND      RECIP_TYPE_CD = '10'
AND      BENEF_SEQ_NBR = 1 
GROUP BY AGTY_SYS_CD 
order by 2 desc

query 2

SELECT   AGTY_SYS_CD, count(*)  AS "NEW RETIREES" 
FROM     DSNP.PR01_T_RECIP_SYS 
WHERE    RECIP_RETIR_DT BETWEEN '2010-02-01' and '2010-02-28'
AND      BENEF_STAT_CD IN ('AC', 'DP') 
AND      RECIP_TYPE_CD = '10'
AND      BENEF_SEQ_NBR = 1 
GROUP BY AGTY_SYS_CD 
ORDER BY 2 DESC

The only difference between the two is the dates. I want my query to have one column for Total and one for New Retirees.


Can someone show me how to combine these using derived tables? I gave it a shot , but I don't know how to reference Alias named fields in the main query. Thanks!

 

[markros]

Try

SELECT   AGTY_SYS_CD, 
sum(case when  RECIP_RETIR_DT BETWEEN '2010-02-01' and '2010-02-28' then 1 end) AS "NEW RETIREES",
sum(case when RECIP_RETIR_DT <= '2010-02-01' then 1 end) AS "TOTAL RETIREES"

 FROM     DSNP.PR01_T_RECIP_SYS 
WHERE   BENEF_STAT_CD IN ('AC', 'DP') AND      RECIP_TYPE_CD = '10'AND      BENEF_SEQ_NBR = 1 GROUP BY AGTY_SYS_CD ORDER BY 2 DESC

PluralSight Learning Library

 

[SQLBill]

Try using CASE. This is untested...

SELECT   AGTY_SYS_CD, 
  CASE WHEN RECIP_RETIR_DT <= '2010-02-01'
       THEN count(*)  AS "TOTAL RETIREES"
       ELSE count(*) AS "NEW RETIREEES"
FROM     DSNP.PR01_T_RECIP_SYS
WHERE    BENEF_STAT_CD IN ('AC', 'DP')
AND      RECIP_TYPE_CD = '10'
AND      BENEF_SEQ_NBR = 1
GROUP BY AGTY_SYS_CD
order by 2 desc

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLBill]

Dang - Markros was faster and more accurate than me. Oh well...


-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[markros]

Now I understand why it took me so many tries to post this

I clicked Submit button at least 5 times - it didn't work

PluralSight Learning Library

 

[lrdave36]

Thanks Markros. Yours works great. Can this be done with derived tables though? I'm trying to practice more with that technique. I came up with this, but it errors out on me:

SELECT   AGTY_SYS_CD,
         A.TOTAL RETIREES,
         B.NEW RETIREES
          
FROM (SELECT AGTY_SYS_CD, count(*) as "TOTAL RETIREES"


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

(

SELECT   AGTY_SYS_CD, count(*)  AS "NEW RETIREES" 
FROM     DSNP.PR01_T_RECIP_SYS 
WHERE    RECIP_RETIR_DT BETWEEN '2010-02-01' and '2010-02-28'
AND      BENEF_STAT_CD IN ('AC', 'DP') 
AND      RECIP_TYPE_CD = '10'
AND      BENEF_SEQ_NBR = 1 
GROUP BY AGTY_SYS_CD ) as B

FROM DSNP.PR01_T_RECIP_SYS
WHERE A.RECIP_SSN_NBR = B.RECIP_SSN_NBR

ORDER BY 2 DESC

 

[markros]

SELECT   A.AGTY_SYS_CD,
         A.TOTAL RETIREES,
         B.NEW RETIREES
          
FROM (SELECT AGTY_SYS_CD, 
count(*) as "TOTAL RETIREES"


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

INNER JOIN 
(

SELECT   AGTY_SYS_CD, count(*)  AS "NEW RETIREES"
FROM     DSNP.PR01_T_RECIP_SYS
WHERE    RECIP_RETIR_DT BETWEEN '2010-02-01' and '2010-02-28'
AND      BENEF_STAT_CD IN ('AC', 'DP')
AND      RECIP_TYPE_CD = '10'
AND      BENEF_SEQ_NBR = 1
GROUP BY AGTY_SYS_CD ) as B


ON A.AGTY_SYS_CD = B.AGTY_SYS_CD

ORDER BY 2 DESC

PluralSight Learning Library

 

[lrdave36]

Thanks again, Markros! So with derived tables, I can only join on a field that is being referenced in the inner selects? Is this why you joined on agty_sys_cd instead of recip_ssn_nbr?

I did have to go with a LEFT OUTER JOIN as the B table wouldn't show all the agencies if the count was 0.

 

[markros]

Yes, you can only use fields from inner selects - you can not use fields from the tables that form that select - they don't exist anymore as far as query is concerned.

The original code we used is more efficient here, than derived tables approach - I just showed you the principle.

PluralSight Learning Library