CODEWITHSUNDEEP
sqlsql-server
N. Staatz
N. Staatz

Reputation: 15

returning null for dates SQL

i have the following query:

select EL.Datetimestart, E.Fullname, VisaExpiryDate, E.EmployeeStatusCode
from EmployeeLog as EL
inner join Employee as E on E.Employeekey = EL.EmployeeKey
where DateTimeStart >= Getdate() -1 and VisaExpiryDate <=GetDate() and E.EmployeeStatusCode <> 'x' 
order by VisaExpiryDate

At the moment this will return employees who have a expired visa and are working on that day,

VisaExpiryDate - 2017-08-08 00:00:00.000
DateTimeStart - 2017-08-10 08:00:03.090
FullName - Rypsuke
EmployeeStatusCode - C

Visa information is checked and inserted into the system and this can take a couple days, so new employees get a 'NULL' as the field is left blank.

How do i return these nulls to see both who haven't yet had their visas checked and people with expired visas?

thanks for any help Nick

Upvotes: 0

Views: 107

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

If I understand correctly, you can just add a condition to the where clause:

select EL.Datetimestart, E.Fullname, VisaExpiryDate, E.EmployeeStatusCode
from EmployeeLog EL inner join
     Employee E
     on E.Employeekey = EL.EmployeeKey
where DateTimeStart >= Getdate() - 1 and
      (VisaExpiryDate <= GetDate() or VisaExpiryDate is null) and
      E.EmployeeStatusCode <> 'x' 
order by VisaExpiryDate;

Upvotes: 1

DVT
DVT

Reputation: 3127

Use IS NULL 

select EL.Datetimestart, E.Fullname, VisaExpiryDate, E.EmployeeStatusCode
from EmployeeLog as EL
inner join Employee as E on E.Employeekey = EL.EmployeeKey
where DateTimeStart >= Getdate() -1 and (VisaExpiryDate <=GetDate() OR VisaExpiryDate IS NULL)and E.EmployeeStatusCode <> 'x' 
order by VisaExpiryDate

Upvotes: 2

Related Questions