Link to home
Start Free TrialLog in
Avatar of Say55ow
Say55owFlag for United States of America

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
Avatar of chapmandew
chapmandew
Flag of United States of America image

select *
from tablename
where
(sdate is not null or edate is not null) and
type = 2
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))

Open in new window

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
                  )
ASKER CERTIFIED SOLUTION
Avatar of Ram4020
Ram4020
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Say55ow

ASKER

thanks for your help!