CODEWITHSUNDEEP
sqlsql-server
jaymin
jaymin

Reputation: 25

SQL FOR DATA BETWEEN JOIN DATE and LEAVING DATE OF EMPLOYEE

I have one Employee who has joining two time.Please review below table.

EmpID    EmpName    DateOFJoin    DateOfLeaving    Status
1         XYZ       2015-10-01    2017-09-26       De-Active
2         ABC       2018-01-01                     Active                      
3         XYZ       2018-10-15                     Active

I want output like For Instance, I have FromDate and ToDate like '2019-12-01' and '2019-12-31' 

EmpID    EmpName    DateOFJoin    DateOfLeaving    Status
2         ABC       2018-01-01                     Active 
3         XYZ       2018-10-15                     Active

IF I have FromDate and ToDate like '2017-08-01' and '2017-09-30' 

EmpID    EmpName    DateOFJoin    DateOfLeaving    Status
1         XYZ       2015-10-01    2017-09-26       De-Active

IF I have FromDate and ToDate like '2018-01-01' and '2018-03-31' 

EmpID    EmpName    DateOFJoin    DateOfLeaving    Status
2         ABC       2018-01-01                     Active

Please Help to prepare SQL For it.

Upvotes: 2

Views: 1052

Answers (4)

DarkRob
DarkRob

Reputation: 3833

You may try this in your process:

   GO
   select * into #temptable from 
   (select EmpID,EmpName,DateOFJoin,DateOfLeaving,Status from table 
   where (DateOFJoin between @startdate and @enddate) or ( DateOfLeaving between @startdate and @enddate) ) as d

   if exists (select Empid from #temptable)
   Begin
   select EmpID,EmpName,DateOFJoin,DateOfLeaving,Status from #temptable
   End
   Else
   Begin
   select EmpID,EmpName,DateOFJoin,DateOfLeaving,Status from table where DateOfLeaving is null
   End
   drop table #temptable
   GO

Upvotes: 0

StepUp
StepUp

Reputation: 38209

try to use WHERE with ISNULL():

SELECT 
* 
FROM yourTable t
WHERE T.DateOfJoin <=@FromDate AND ISNULL(T.DateOfLeaving, @ToDate)>=@ToDate

Let me show an example:

DECLARE @table TABLE 
(
EmpID int, 
EmpName varchar(50),    
DateOfJoin datetime,
DateOfLeaving DATETIME,
StatusName VARCHAR(50)
)

INSERT INTO @table
(
    EmpID,
    EmpName,
    DateOfJoin,
    DateOfLeaving,
    StatusName
)
VALUES
 (1, 'XYZ',      '2015-10-01',    '2017-09-26', 'De-ACTIVE')
,(2, 'ABC',      '2018-01-01',    NULL, 'ACTIVE')                      
,(3, 'XYZ',      '2018-10-15',    NULL, 'ACTIVE')    


DECLARE   @FromDate  VARCHAR(50) = '2019-12-01'
        , @ToDate  VARCHAR(50) = '2019-12-31'
SELECT 
* 
FROM @table t
WHERE T.DateOfJoin <=@FromDate AND ISNULL(T.DateOfLeaving, @ToDate)>=@ToDate

Upvotes: 0

Fahmi
Fahmi

Reputation: 37493

Use coalesce() function

DEMO

select EmpID,EmpName,DateOFJoin,DateOfLeaving,Status
from tablename
where DateOFJoin<=param1 and coalesce(DateOfLeaving,param2)>=param2

Upvotes: 2

Pham X. Bach
Pham X. Bach

Reputation: 5442

You could use this:

SELECT * 
FROM table_name
WHERE DateOFJoin <= ToDate AND COALESCE(DateOfLeaving, FromDate) >= FromDate;

Upvotes: 0

Related Questions