select query help

[yan19454]

EmplID Status License Verify Expired
11112 OR RN Y 7/17/07
11113 ER DR N
11114 PP DR Y 1/1/07
11115 OO DR 7/1/07

If the end user select 11112, it will return the record
11112 OR RN Y 7/17/07
If the end user select 11113, it will return the record
11113 ER

Since the Verify is N.
If the end user select 11114, it will return the record
11114 PP
Since it is expired.
If the end user select 11115, it will return the record
11115 OO
Since it is not verify.

I want to get the status , not expired and verify license out from license table with paremeter as selected empID ,

How I will write this query in sql.

 

[rac2]

SELECT EmplID, Status,
       CASE
         WHEN Verify = 'Y'
              AND Expired IS NOT NULL
              AND DATEDIFF(DAY, getdate(), Expired ) > 0
           THEN License
         ELSE ''
       END AS "License",
       CASE
         WHEN Verify = 'Y'
              AND Expired IS NOT NULL
              AND DATEDIFF(DAY, getdate(), Expired ) > 0
           THEN CONVERT(CHAR(10),Expired, 110)
         ELSE ''
       END AS "Expires"
FROM MyTable
WHERE EmplID = @employeeID

 

[okiiyama]

Would this work?

select a.empid, a.status, b.license, b.verify, b.expired
from some_table a
left outer join some_table b on a.empid = b.empid and verify = 'y' and expired > getDate()
where a.empid = 'some_empid'