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

    Unanswered: Need Help Combining Queries

    Hey guys,

    I am stuck trying to figure out how to combine several queries into one master query. First here is my main query:


    Code:
    SELECT   A.AGTY_SYS_CD, A.BENEF_STAT_CD, 
             sum(case when A.RECIP_RETIR_DT <= '2010-04-01' then 1 end)   AS "TOTAL RETIREES", 
             sum(case when recip_retir_dt2 = '2010-05-01' and A.BENEF_SEQ_NBR = 1 then 1 end)  AS "NEW DROPS", 
             sum(case when A.BENEF_STAT_CD = 'AC' and RECIP_TYPE_CD in ('10') and  RECIP_RETIR_DT BETWEEN '2010-03-01' and '2010-03-30' then 1 end)   AS "A&S RETIREES", 
             sum(case When RIGHT (VOUCHER_ID_CD,1) = 'D' and A.RECIP_RETIR_DT BETWEEN '2010-03-01' and '2010-03-30' then 1 end)   AS "DISABILITIES" 
    
    
    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.RECIP_TYPE_CD = '10' AND      A.`BENEF_SEQ_NBR = 1 
    GROUP BY A.AGTY_SYS_CD, A.BENEF_STAT_CD 
    ORDER BY 3 DESC

    I discovered this week that I have to base my search criteria on a different field being min(anty_pymt_dt) = '2010-05-01 instead of retire date for these categories. This causes a problem because I have to use a derived table with the min function.

    So for disabilities, the code is


    Code:
    SELECT count(A.recip_ssn_nbr) as "DISABILITIES"  FROM 
    
    
    
    (SELECT A.recip_ssn_nbr FROM 
     dsnp.pr01_t_anty_pymt A,
         dsnp.pr01_t_recip_sys B
    where A.recip_ssn_nbr = B.recip_ssn_nbr
    and right(B.voucher_id_cd,1) = 'D'
    group by A.recip_ssn_nbr
    having min(A.anty_pymt_dt) = '2010-05-01') as A
    Is there any way I can plug this code into my CASE statement?

    The other way I was thinking was to make each category a derived table, but I don't know how to perform multiple inner joins. I tried a test UNION with this query:

    Code:
    
    SELECT count(A.recip_ssn_nbr) as "DISABILITIES"  FROM 
    
    
    
    (SELECT A.recip_ssn_nbr FROM 
     dsnp.pr01_t_anty_pymt A,
         dsnp.pr01_t_recip_sys B
    where A.recip_ssn_nbr = B.recip_ssn_nbr
    and right(B.voucher_id_cd,1) = 'D'
    group by A.recip_ssn_nbr
    having min(A.anty_pymt_dt) = '2010-05-01') as A
    
    
    UNION ALL
    
    SELECT count(A.recip_ssn_nbr) as "TOTAL RETIREES"
    
    FROM
     (SELECT *
       FROM     DSNP.PR01_T_RECIP_SYS
       WHERE    RECIP_RETIR_DT <= '2010-04-01'
       AND      BENEF_STAT_CD IN ('AC', 'DP')
       AND      RECIP_TYPE_CD = '10'
       AND      BENEF_SEQ_NBR = 1
        ) as A

    However, the output doesn't give me different column names. It only shows Disabilities as the column name.

    Can anyone help? Thanks!

  2. #2
    Join Date
    Mar 2010
    Posts
    32
    Hey guys,

    I made some progress and figured a way to combine the queries by referencing my alias names inside derived tables.

    It would still be easier for me if I could use that case statement so if anyone knows a way to use min function inside case statement, that would be great.

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow No name if different

    If you want to have name on the column after UNION ALL you have to use the same name on all parts:

    Code:
    SELECT count(A.recip_ssn_nbr) as "DISABILITIES"  
    FROM 
    (SELECT 1
    FROM 
     dsnp.pr01_t_anty_pymt A,
         dsnp.pr01_t_recip_sys B
    where A.recip_ssn_nbr = B.recip_ssn_nbr
    and right(B.voucher_id_cd,1) = 'D'
    group by A.recip_ssn_nbr
    having min(A.anty_pymt_dt) = '2010-05-01') as A
    
    
    UNION ALL
    
    SELECT count(A.recip_ssn_nbr) as "DISABILITIES"
    
    FROM
     (SELECT 1
       FROM     DSNP.PR01_T_RECIP_SYS
       WHERE    RECIP_RETIR_DT <= '2010-04-01'
       AND      BENEF_STAT_CD IN ('AC', 'DP')
       AND      RECIP_TYPE_CD = '10'
       AND      BENEF_SEQ_NBR = 1
        ) as A
    Lenny

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you want to have name on the column after UNION ALL you have to use the same name on all parts:
    One way to use the same names on all parts would be like this:
    Code:
    SELECT
           'DISABILITIES'         AS item_name
         , count(A.recip_ssn_nbr) as value_of_item
      FROM 
    ...
     
    UNION ALL
     
    SELECT
           'TOTAL RETIREES'       AS item_name
         , count(A.recip_ssn_nbr) AS value_of_item
      FROM
    ...

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Is it not necessary to specify codes in comments in the following example?:

    Code:
    (
    SELECT
    /*
           AGTY_SYS_CD
         , BENEF_STAT_CD
         ,
    */
           'DISABILITIES'         AS item_name
         , count(A.recip_ssn_nbr) as value_of_item
      FROM 
      (SELECT A.recip_ssn_nbr
    /*
            , B.AGTY_SYS_CD
            , B.BENEF_STAT_CD 
    */
         FROM dsnp.pr01_t_anty_pymt A
            , dsnp.pr01_t_recip_sys B
        WHERE A.recip_ssn_nbr = B.recip_ssn_nbr
          AND right(B.voucher_id_cd,1) = 'D'
    /*
          AND B.BENEF_STAT_CD IN ('AC', 'DP')
          AND B.RECIP_TYPE_CD = '10'
          AND B.BENEF_SEQ_NBR = 1
    */
        GROUP BY
              A.recip_ssn_nbr
    /*
            , B.AGTY_SYS_CD
            , B.BENEF_STAT_CD 
    */
       HAVING min(A.anty_pymt_dt) = '2010-05-01'
      ) as A
    /*
     GROUP BY
           AGTY_SYS_CD
         , BENEF_STAT_CD 
    */
    
    UNION ALL
    
    SELECT
    /*
           AGTY_SYS_CD
         , BENEF_STAT_CD
         ,
    */
           'TOTAL RETIREES'       AS item_name
         , count(A.recip_ssn_nbr) AS value_of_item
      FROM
      (SELECT *
         FROM DSNP.PR01_T_RECIP_SYS
        WHERE RECIP_RETIR_DT <= '2010-04-01'
          AND BENEF_STAT_CD IN ('AC', 'DP')
          AND RECIP_TYPE_CD = '10'
          AND BENEF_SEQ_NBR = 1
      ) as A
    /*
     GROUP BY
           AGTY_SYS_CD
         , BENEF_STAT_CD 
    */
    )
    ORDER BY 1
        /* , 2 , 3 */
    ;
    Last edited by tonkuma; 05-06-10 at 17:49. Reason: Modified and added red parts

Posting Permissions

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