Say55ow
asked on
SQL Syntax
Each key has three types.
I would only like to display the keys where the sDate or eDate IS NOT NULL and its a type 2.
If the key's sDate or eDate IS NOT NULL and its a type 3 then skip all records pertaining to that key.
key type sDate eDate
12 1 12/01/09 12/02/2009
12 2 12/01/09 12/02/2009
12 3 12/01/09 12/02/2009
15 1 12/01/09 12/02/2009
15 2 NULL NULL
15 3 NULL NULL
20 1 12/01/09 12/02/2009
20 2 12/01/09 12/02/2009
20 3 NULL NULL
Desired output
key type sDate eDate
20 1 12/01/09 12/02/2009
20 2 12/01/09 12/02/2009
I would only like to display the keys where the sDate or eDate IS NOT NULL and its a type 2.
If the key's sDate or eDate IS NOT NULL and its a type 3 then skip all records pertaining to that key.
key type sDate eDate
12 1 12/01/09 12/02/2009
12 2 12/01/09 12/02/2009
12 3 12/01/09 12/02/2009
15 1 12/01/09 12/02/2009
15 2 NULL NULL
15 3 NULL NULL
20 1 12/01/09 12/02/2009
20 2 12/01/09 12/02/2009
20 3 NULL NULL
Desired output
key type sDate eDate
20 1 12/01/09 12/02/2009
20 2 12/01/09 12/02/2009
try this
SELECT A.[key], A.[type], A.sDate, A.eDate
FROM youTable A
WHERE A.[type] = 2
AND (A.sDate IS NOT NULL OR A.eDate IS NOT NULL)
AND NOT EXISTS (SELECT NULL FROM yourTable B WHERE A.[key] = B.[key] AND B.[type] = 3 AND (B.sDate IS NOT NULL OR B.eDate IS NOT NULL))
SELECT * FROM Table1
WHERE
[key] in
(
SELECT [key] FROM Table1 WHERE sDate is not null AND eDate is not null AND [type] = 2
EXCEPT
SELECT [key] FROM Table1 WHERE sDate is not null AND eDate is not null AND [type] = 3
)
WHERE
[key] in
(
SELECT [key] FROM Table1 WHERE sDate is not null AND eDate is not null AND [type] = 2
EXCEPT
SELECT [key] FROM Table1 WHERE sDate is not null AND eDate is not null AND [type] = 3
)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks for your help!
from tablename
where
(sdate is not null or edate is not null) and
type = 2