SQL，用于加入日期和离开员工日期之间的数据

Question

我有一个加入了两个时间的员工。请评论下面。

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     

我想要输出例如，我有源于" 2019-12-01"one_answers" 2019-12-31"

的源头

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

如果我有源自源和托特，例如'2017-08-01'和'2017-09-30'

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

如果我有源源不断和托特，例如'2018-01-01'和'2018-03-31'

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

请帮助为此准备SQL。

Answer 1

使用coalesce()功能

演示

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

Answer 2

您可以使用以下方式：

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

Answer 3

尝试将 WHERE与 ISNULL()：

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

让我展示一个例子：

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

Answer 4

您可以在过程中尝试此操作：

   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