时间戳

首页 > 解决方案 > 在日期范围之间分配天数

sql - 在日期范围之间分配天数

问题描述

我有一张这样的桌子：

LeaveRequestID | DateAllocated    | DateFrom    | DateTo     | TotalLeaveDays
1              |  2020-03-02      | 2020-03-02  | 2020-03-05 |  3
2              |  2020-03-02      | 2020-03-02  | 2020-03-04 |  2

这是我的查询：

SELECT 
    t1.DateAllocated,
    t2.DateFrom,
    t2.DateTo,
    DATEDIFF(DAY, t2.DateFrom, t2.DateTo) TotalLeaveDays 
FROM 
    tblLeave_Allocation t1
INNER JOIN 
    tblLeave_Requests t2 ON CONVERT(VARCHAR, t1.DateAllocated, 23) = CONVERT(VARCHAR, t2.DateFrom, 23)

这是我想要的输出：

LeaveRequestID  | DateAllocated | DateFrom    | DateTo     | TotalLeaveDays | DateDist   | LeaveCount
1               | 2020-03-02    | 2020-03-02  | 2020-03-05 |    3           | 2020-03-03 | 1
1               | 2020-03-02    | 2020-03-02  | 2020-03-05 |    3           | 2020-03-04 | 1
1               | 2020-03-02    | 2020-03-02  | 2020-03-05 |    3           | 2020-03-05 | 1
2               | 2020-03-02    | 2020-03-02  | 2020-03-04 |    2           | 2020-03-03 | 1
2               | 2020-03-02    | 2020-03-02  | 2020-03-04 |    2           | 2020-03-04 | 1

我想TotalLeaveDays从后天开始为每个计数DateFrom分配每个单曲计数DateTo。如何达到我想要的输出？

标签： sqlsql-server

解决方案

递归 CTE 似乎很适合这个：

with cte as (
      select LeaveRequestID, DateAllocated, DateFrom, DateTo, TotalLeaveDays,
             1 as n, dateadd(day, 1, datefrom) as datedist
      from t
      union all
      select LeaveRequestID, DateAllocated, DateFrom, DateTo, TotalLeaveDays,
             n + 1, dateadd(day, 1, datedist) as datedist
      from cte
      where n < TotalLeaveDays
     )
select LeaveRequestID, DateAllocated, DateFrom, DateTo, TotalLeaveDays, datedist, 1
from cte;

推荐阅读