Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2009
    Posts
    46

    Unanswered: How Can I Get Detail on this Query?

    Hey guys,

    I have the following query which gives me total number of SSNS per county along with other info:

    Code:
    SELECT CTY_ID_NM              AS COUNTY_NAME                                 
              ,COUNT(*)               AS COUNT                                       
              ,SUM(ANTY_PYMT_TOT_AMT) AS TOTAL_AMOUNT                                
          FROM (SELECT D.CTY_ID_NM            AS CTY_ID_NM                           
                      ,A.RECIP_SSN_NBR        AS RECIP_SSN_NBR                       
                      ,SUM(ANTY_PYMT_TOT_AMT) AS ANTY_PYMT_TOT_AMT                   
                 FROM DSNP.PR01_T_RECIP_SYS A                                        
                     ,DSNP.PR01_T_ANTY_PYMT B                                        
                     ,DSNP.PR01_T_MBR_CITY  C                                        
                     ,DSNP.PR01_T_CTY       D                                        
                WHERE A.RECIP_SSN_NBR = B.RECIP_SSN_NBR                              
                  AND A.BENEF_SEQ_NBR = B.BENEF_SEQ_NBR                              
                  AND A.RECIP_SSN_NBR = C.MBR_SSN_NBR                                
                  AND C.CTY_ID_CD     = D.CTY_ID_CD                                  
                  AND A.RECIP_TYPE_CD = '10'    
                  AND B.ANTY_PYMT_DT BETWEEN '2007-07-01'                            
                  AND '2008-06-30'                            
                  AND B.ANTY_PYMT_STAT_CD = 'A'                                      
                  AND C.MBR_ADDR_SEQ_NBR  = 1                                        
                  GROUP BY                                                             
                  D.CTY_ID_NM                                                    
                 ,A.RECIP_SSN_NBR                                                
               UNION ALL                                                            
               SELECT D.CTY_ID_NM            AS CTY_ID_NM                           
               ,A.RECIP_SSN_NBR        AS RECIP_SSN_NBR                       
               ,SUM(ANTY_PYMT_TOT_AMT) AS ANTY_PYMT_TOT_AMT                   
               FROM DSNP.PR01_T_RECIP_SYS  A                                       
              ,DSNP.PR01_T_ANTY_PYMT  B                                       
              ,DSNP.PR01_T_RECIP_CITY C                                       
              ,DSNP.PR01_T_CTY        D                                       
              WHERE A.RECIP_SSN_NBR = B.RECIP_SSN_NBR                              
              AND A.BENEF_SEQ_NBR = B.BENEF_SEQ_NBR                              
              AND A.RECIP_SSN_NBR = C.RECIP_SSN_NBR     
              AND C.CTY_ID_CD     = D.CTY_ID_CD                                  
              AND A.RECIP_TYPE_CD IN ('20', '30', '40')                          
              AND B.ANTY_PYMT_DT BETWEEN '2007-07-01'                            
              AND '2008-06-30'                            
              AND B.ANTY_PYMT_STAT_CD = 'A'                                      
              AND C.RECIP_ADDR_SEQ_NBR  = 1                                      
             GROUP BY                                                             
             D.CTY_ID_NM                                                    
            ,A.RECIP_SSN_NBR                                                
             ) AS A                                                                
    GROUP BY                                                                   
    A.CTY_ID_NM

    The output looks like this:

    COUNTY COUNT TOTAL AMOUNT

    BENTON 234 $23,999.



    I need to modify the query to show me each SSN number, their city name (CITY_ID_NM), and state (STATE_ID_CD) which makes up these count totals.

    I modified the query like this which does give me the SSN's:


    Code:
    SELECT CTY_ID_NM              AS COUNTY_NAME                                 
              ,RECIP_SSN_NBR as SSN
              
                                                     
                                          
          FROM (SELECT D.CTY_ID_NM            AS CTY_ID_NM                           
                      ,A.RECIP_SSN_NBR        AS RECIP_SSN_NBR                       
                      ,SUM(ANTY_PYMT_TOT_AMT) AS ANTY_PYMT_TOT_AMT                   
                 FROM DSNP.PR01_T_RECIP_SYS A                                        
                     ,DSNP.PR01_T_ANTY_PYMT B                                        
                     ,DSNP.PR01_T_MBR_CITY  C                                        
                     ,DSNP.PR01_T_CTY       D                                        
                WHERE A.RECIP_SSN_NBR = B.RECIP_SSN_NBR                              
                  AND A.BENEF_SEQ_NBR = B.BENEF_SEQ_NBR                              
                  AND A.RECIP_SSN_NBR = C.MBR_SSN_NBR                                
                  AND C.CTY_ID_CD     = D.CTY_ID_CD                                  
                  AND A.RECIP_TYPE_CD = '10'    
                  AND B.ANTY_PYMT_DT BETWEEN '2007-07-01'                            
                  AND '2008-06-30'                            
                  AND B.ANTY_PYMT_STAT_CD = 'A'                                      
                  AND C.MBR_ADDR_SEQ_NBR  = 1                                        
                  GROUP BY                                                             
                  D.CTY_ID_NM                                                    
                 ,A.RECIP_SSN_NBR                                                
               UNION ALL                                                            
               SELECT D.CTY_ID_NM            AS CTY_ID_NM                           
               ,A.RECIP_SSN_NBR        AS RECIP_SSN_NBR                       
               ,SUM(ANTY_PYMT_TOT_AMT) AS ANTY_PYMT_TOT_AMT                   
               FROM DSNP.PR01_T_RECIP_SYS  A                                       
              ,DSNP.PR01_T_ANTY_PYMT  B                                       
              ,DSNP.PR01_T_RECIP_CITY C                                       
              ,DSNP.PR01_T_CTY        D                                       
              WHERE A.RECIP_SSN_NBR = B.RECIP_SSN_NBR                              
              AND A.BENEF_SEQ_NBR = B.BENEF_SEQ_NBR                              
              AND A.RECIP_SSN_NBR = C.RECIP_SSN_NBR     
              AND C.CTY_ID_CD     = D.CTY_ID_CD                                  
              AND A.RECIP_TYPE_CD IN ('20', '30', '40')                          
              AND B.ANTY_PYMT_DT BETWEEN '2007-07-01'                            
              AND '2008-06-30'                            
              AND B.ANTY_PYMT_STAT_CD = 'A'                                      
              AND C.RECIP_ADDR_SEQ_NBR  = 1                                      
             GROUP BY                                                             
             D.CTY_ID_NM                                                    
            ,A.RECIP_SSN_NBR                                                
             ) AS A

    However, when I try to add in the city and state fields, SQL tells me it is not valid in the context. I don't know how to reference these fields outside the sub-query because the UNION references this data in two different tables called PR01_T_RECIP_CITY and PR01_T_MBR_CITY.

    Anyone have any idea how I can add the SSN's city and state to my query result?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Add
    MAX(recip_CITY_ID_NM) AS city_id_nm
    MAX(recip_STATE_ID_CD) AS state_id_cd
    and
    MAX(mbr_CITY_ID_NM) AS city_id_nm
    MAX(mbr_STATE_ID_CD) AS state_id_cd

    to each sub-query.

Posting Permissions

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