CODEWITHSUNDEEP
sqlsql-serverdate
user3115933
user3115933

Reputation: 4463

Extract a list of all active employees (records) as at a particular date?

I am using SQL Server 2012 and have a table called tbEmployees. An extract of this table is shown below:

 empID     HiredDate      LeftDate
  108      2014-05-11     2016-04-20
  109      2015-05-14     2015-12-28
  ...

How can I get a list of all active employees as at '2018-07-01'? In other terms, I want to extract all employees who were active in the company as at that particular date.

I have tried this syntax but I am not sure it is extracting the right information:

 SELECT * FROM tblEmployee
 WHERE HiredDate <= '2018-07-01' and LeftDate >= '2018-07-01'

Upvotes: 0

Views: 285

Answers (4)

SQL PLSQL
SQL PLSQL

Reputation: 85

 SELECT * FROM tblEmployee WHERE HiredDate <= '2018-07-01' and LeftDate >= '2018-07-01'

Your above statement will suppose to work as it will automatically convert the string '2018-07-01' into the DateTime format for a date of 2018-07-01 00:00:00.

Or maybe can can also try 

     SELECT * FROM tblEmployee WHERE HiredDate <= '01/07/2018' and LeftDate >= '01/07/2018'

Upvotes: 0

SAS
SAS

Reputation: 4045

Just a little tweak to your solution, to include those still hired.

DECLARE @Date DATE = '2018-07-01'

SELECT *
FROM tblEmployee
WHERE HiredDate <= @Date
 AND (LeftDate >= @Date OR LeftDate IS NULL)

If you want all that were hired during all of a timespan instead of a single date:

DECLARE @StartDate DATE = '2018-07-01'
DECLARE @EndDate DATE = '2018-07-02'

SELECT *
FROM tblEmployee
WHERE HiredDate <= @EndDate
 AND (LeftDate >= @StartDate OR LeftDate IS NULL)

Or those that were hired at any portion of the date span:

DECLARE @StartDate DATE = '2018-07-01'
DECLARE @EndDate DATE = '2018-07-02'

SELECT *
FROM tblEmployee
WHERE HiredDate BETWEEN @StartDate AND @EndDate
 OR LeftDate BETWEEN @StartDate AND @EndDate
 OR (HiredDate <= @EndDate AND LeftDate IS NULL)

Upvotes: 0

forpas
forpas

Reputation: 164214

With BETWEEN:

SELECT * FROM tblEmployee
WHERE 
  ('2018-07-01' BETWEEN HiredDate AND LeftDate)
  OR
  ('2018-07-01' >= HiredDate AND LeftDate IS NULL)

The condition LeftDate IS NULL just in case the employee is still active.

Upvotes: 0

GarethD
GarethD

Reputation: 69819

What you had seems fine to me, other than it may not consider employees that are still active (i.e. has no value for LeftDate). You may need to include an OR condition to capture this:

SELECT * 
FROM tblEmployee
WHERE HiredDate <= '2018-07-01' 
AND (LeftDate >= '2018-07-01' OR LeftDate IS NULL);

Upvotes: 1

Related Questions