CODEWITHSUNDEEP
sql-servert-sql
NewbieSQL_Germany
NewbieSQL_Germany

Reputation: 85

T-SQL : filter out all employees who left before > 6 months

I need to create a new employee-database that filters out all employees, who left the company longer than 6 months ago from today.

I have a table with entry date, exit date and tried something like :

 WHERE [exit date] > = DATEADD(M, -6, getdate())

That didn't work because it shows only the employees who left the company longer than 6 months ago. I just want to filter them out automatically and only show the employees, who are still employed er left the company lesser than 6 months ago.

Thanks in advance for your help.

Upvotes: 0

Views: 60

Answers (1)

Charlieface
Charlieface

Reputation: 71579

You need to check for null:

WHERE ([exit date] IS NULL OR [exit date] > = DATEADD(M, -6, getdate()))

Do not try tricks with NOT and <

WHERE NOT ([exit date] < DATEADD(M, -6, getdate()))

This doesn't work because NULL rows will just result in UNKNOWN, so those rows will not be returned.

You can do this though, you may find it performs faster or slower than the first version:

WHERE NOT EXISTS (SELECT 1
   WHERE [exit date] < DATEADD(M, -6, getdate())

This works because if exit date is null then no row gets returned from the subquery.

Upvotes: 1

Related Questions