This is what I'm need to get:

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)

select count(cossn) as cossn
from t16pendmvt
where Mvt_typ ='r' and mvt_loc <> 'r%' or mvt_loc='s%' or mvt_loc='v%'

select count(cossn) as cossn
from t16pendmvt
where MVT_Typ ='t'

JJ297 (nc297 (AT) yahoo (DOT) com) writes:
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)


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%')


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 (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx