CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Sajeel
Sajeel

Reputation: 168

SQL Server where clause with null column

I have an Employee table and how it works is, when a new employee is added, the column [DOR] will be null and [Status] will be 1. When the employee is being relieved from the company, the [DOR] column value will be the date which he/she left the company and [Status] is set to 0.

I need to fetch the details of all the employees who were available in a given date. The employees with Status as 1 and those who are not yet relieved till that date have to be fetched.

But I am not able to do the same as when equating with DOR, its null value and not returning any of the rows.

Data in Employee table

If I give the input as 2015-02-10, it should fetch the two records and when I give 2015-02-15, it should fetch only first record.

CREATE TABLE [Employee]
(
    [EmployeeId] [int] IDENTITY(1000,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [RoleId] [int] NOT NULL,
    [Email] [varchar](50) NULL,
    [Contact] [varchar](50) NULL,
    [DOJ] [date] NOT NULL,
    [DOR] [date] NULL,
    [Status] [bit] NOT NULL,
    [Salary] [decimal](18, 2) NULL
)

INSERT [dbo].[Employee] ([EmployeeId], [Name], [RoleId], [Email], [Contact], [DOJ], [DOR], [Status], [Salary])  
VALUES (1001, N'Employee 1', 3, N'', N'', CAST(0x8D390B00 AS Date), NULL, 1, CAST(6000.00 AS Decimal(18, 2)))

INSERT [dbo].[Employee] ([EmployeeId], [Name], [RoleId], [Email], [Contact], [DOJ], [DOR], [Status], [Salary]) 
VALUES (1002, N'Employee 2', 7, N'', N'', CAST(0x8D390B00 AS Date), CAST(0x9A390B00 AS Date), 0, CAST(4000.00 AS Decimal(18, 2)))

Upvotes: 1

Views: 1632

Answers (4)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726987

You need to use IS NULL operator instead of = NULL in your condition, like this:

SELECT *
FROM Employee
WHERE DOJ <= '2015-02-15'
  AND (DOR IS NULL OR DOR > '2015-02-15')

Upvotes: 4

dnoeth
dnoeth

Reputation: 60502

Another variation of the previous answers :-)

SELECT * FROM Employee
WHERE @dateOfSearch BETWEEN DOJ and COALESCE(DOR, @dateOfSearch)

Upvotes: 0

krivtom
krivtom

Reputation: 24916

Try this:

SELECT * FROM Employee
WHERE @dateOfSearch BETWEEN DOJ and COALESCE(DOR, '2099-12-31')

What this query does, is it checks if the search date is between start date and end date. If end date is null then COALESCE function is used to take the very high value date.

Upvotes: 0

James Z
James Z

Reputation: 12317

Something like this?

select
    EmployeeId,
    Name
from 
    Employee 
where
    DOJ <= @searchDate and 
    (DOR is null or DOR > @searchDate)

Upvotes: 1

Related Questions