453c6d76   
   From: esquel@sommarskog.se   
      
   JJ297 (nc297@yahoo.com) writes:   
   > To get the Pending Count of COSSN I need to add the following...   
   >   
   > 1) Use T16pendall table to Count (COSSN), MFT_POSN1_CD = B or D or   
   > (MFT_POSN1_CD = A and AGED_ALIEN_RSW = Y) and COSSN is not = COSSN in   
   > T16pendmvt, and   
   >   
   > (2) use T16pendmvt table to Count (COSSN), (MVT_TYP = R and MVT_LOC   
   > not = R** or S** or V**), and   
   >   
   > (3) use T16pendmvt table to Count (COSSN) and MVT_TYP = T   
   >   
   > So I tried to code them separtely but don't know how to put them   
   > together.   
   >   
   > select count(cossn) as cossn   
   > from t16pendall a   
   > join t16pendmvt b on a.cossn = b.cossn   
   > where (MFT_POSN1_CD = 'b' or MFT_POSN1_CD='d')   
   > or (MFT_POSN1_CD = 'a' and Aged_Alien_RSW='Y') and   
   > Cossn not in the t16pendmvt table (don't know how to write this)   
      
   Maybe you mean   
      
      SELECT COUNT(a.cossn) AS cossn   
      FROM   tp16pendall a   
      WHERE  (a.MFT_POSN1_CD IN ('B', 'C') OR   
              a.MFT_POSN1_CD = 'A' AND a.AGED_ALIEN_RSW = 'Y')   
         AND  NOT EXISTS (SELECT *   
                          FROM   t16pendmvt b   
                          WHERE  a.cossn = b.cossn)   
      
      
   > select count(cossn) as cossn   
   > from t16pendmvt   
   > where Mvt_typ ='r' and mvt_loc <> 'r%' or mvt_loc='s%' or mvt_loc='v%'   
      
   This does not match your description:   
      
     (2) use T16pendmvt table to Count (COSSN), (MVT_TYP = R and MVT_LOC   
      not = R** or S** or V**), and   
      
   Maybe the WHERE clause should be:   
      
     where Mvt_typ ='r'   
      and  not (mvt_loc = 'r%' or mvt_loc = 's%' or mvt_loc='v%')   
      
      
   > select count(cossn) as cossn   
   > from t16pendmvt   
   > where MVT_Typ ='t'   
      
   Your question is not very clear, but it seems that what you are looking   
   for is simply the sum of of the three COUNT queries. You could write   
   this as:   
      
     SELECT COUNT(*)   
     FROM   ( SELECT a.cossn   
              FROM   tp16pendall a   
              WHERE  (a.MFT_POSN1_CD IN ('B', 'C') OR   
                      a.MFT_POSN1_CD = 'A' AND a.AGED_ALIEN_RSW = 'Y')   
               AND  NOT EXISTS (SELECT *   
                                FROM   t16pendmvt b   
                                WHERE  a.cossn = b.cossn)   
              UNION   
              SELECT cossn   
              FROM   t16pendmvt   
              WHERE  Mvt_typ ='r'   
                AND  not (mvt_loc = 'r%' or mvt_loc = 's%' or mvt_loc='v%')   
              UNION   
              SELECT cossn   
              FROM  t16pendmvt   
              WHERE MVT_Typ ='t') AS u   
      
      
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
   SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)