SQL在几个月内找到员工离开天数

Question

我想在选择开始日期= 2017-02-01并离开日期= 2017-02-28时显示员工叶子的叶子。输出显示

EmployeeID Leave_Start_Date Leave_End-Date
--------- ----------------- ---------------
1         2017-01-29        2017-02-01
2         2017-02-01        2017-02-02

我希望输出喜欢

EmployeeID Leave_Start_Date Leave_End-Date
--------- ----------------- ---------------
1         2017-02-01        2017-02-01
2         2017-02-01        2017-02-02

我像那样写查询

DECLARE @StartDate  DATE
,       @EndDate    DATE            
SET     @StartDate='2017-02-01'
SET     @EndDate='2017-02-28'
SELECT      *
FROM        Employee_Leave
WHERE       Leave_Start_Date BETWEEN @StartDate AND @EndDate
OR          Leave_End_Date  BETWEEN @StartDate  AND @EndDate
ORDER BY    Leave_Start_Date;

Answer 1

您可以使用以下查询：

DECLARE @StartDate  DATE
,       @EndDate    DATE            
SET     @StartDate='2017-02-01'
SET     @EndDate='2017-02-28'
SELECT      EmployeeID,
            CASE 
               WHEN Leave_Start_Date < @StartDate THEN @StartDate
               ELSE Leave_Start_Date 
            END AS Leave_Start_Date,
            CASE 
               WHEN Leave_End_Date > @EndDate THEN @EndDate
               ELSE Leave_End_Date 
            END AS Leave_End_Date          
FROM        Employee_Leave 
WHERE       Leave_Start_Date <= @EndDate AND
            Leave_End_Date >= @StartDate
ORDER BY    Leave_Start_Date;

查询截断[Leave_Start_Date, Leave_End_Date]间隔以匹配[@StartDate, @EndDate]间隔。

演示此处

Answer 2

我建议使用 >=和 <=操作员，然后铸造（列名）到日期格式

下面的示例：

SELECT * FROM sys.sys_config where cast(set_time as date) <= "2016-09-25" and cast(set_time as date) >= "2016-09-01"