same table query...
same table query...
(OP)
Hi,
I'm trying to compare 2 sets of data from the same table.
for example
status Recd# Date
COM 1000 08/20/2008
COM 1001 08/20/2008
AVL 1002 08/20/2008
AVL 1000 08/20/2008
AVL 1001 08/20/2008
The result i'm looking for is records with AVL status that are not in COM status. in this case
AVL 1002 08/20/2008
I tried to do subqueries but it did not work at all
something like
select recd# from table
where date = today and status = "AVL" and recd# NOT IN (Select recd# from table where Status= "COM" and date = today)
but return 0 records...
I'll appreciate any help.
thanks!
where
I'm trying to compare 2 sets of data from the same table.
for example
status Recd# Date
COM 1000 08/20/2008
COM 1001 08/20/2008
AVL 1002 08/20/2008
AVL 1000 08/20/2008
AVL 1001 08/20/2008
The result i'm looking for is records with AVL status that are not in COM status. in this case
AVL 1002 08/20/2008
I tried to do subqueries but it did not work at all
something like
select recd# from table
where date = today and status = "AVL" and recd# NOT IN (Select recd# from table where Status= "COM" and date = today)
but return 0 records...
I'll appreciate any help.
thanks!
where
Talk To Other Members
RE: same table query...
FROM table
WHERE Date=TODAY AND status IN('AVL','COM')
GROUP BY Recd#
HAVING COUNT(*)=1 AND MIN(status)='AVL'
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: same table query...