在“开始日期”和“结束日期”范围之间重复行

Question

嗨，我正在离开带有"开始"和"结束日期"列的表，其中没有应用休假天数。我想重复"开始"和"结束日期"之间的行。

实际结果

leaveid fromdate    Todate       noofdays
1       01/01/2019  02/01/2019      2

例外结果

  leveid  fromdate      Todate      
     1     01/01/2019   01/01/2019 
      2    02/01/2019    02/01/2019

Answer 1

您可以使用

master..[spt_values]生成所需的行数，并使用Dateadd生成日期，如下所示。

;with cte 
     as (select number 
         from   master..[spt_values] 
         where  type = 'p') 
select number+1 as leaveid, 
       Dateadd(day, number, fromdate) fromdate, 
       Dateadd(day, number, fromdate) todays 
from   @table t 
       inner join cte c 
               on c.number < t.noofdays 

在线演示

如果您不想使用 master..[spt_values] ，您也可以使用递归CTE如下所示。

declare @maxNoOfDay int = (select max(noofdays) from @table)
;with seq( number ) as
(
    Select 0 as number
        union all
    Select number + 1
        from seq
        where number <= @maxNoOfDay
)
select number+1 as leaveid, 
       Dateadd(day, number, fromdate) fromdate, 
       Dateadd(day, number, fromdate) todays 
from   @table t 
       inner join seq c 
               on c.number < t.noofdays 

输出

+---------+-------------------------+-------------------------+
| leaveid | fromdate                | todays                  |
+---------+-------------------------+-------------------------+
| 1       | 2019-01-01 00:00:00.000 | 2019-01-01 00:00:00.000 |
+---------+-------------------------+-------------------------+
| 2       | 2019-01-02 00:00:00.000 | 2019-01-02 00:00:00.000 |
+---------+-------------------------+-------------------------+

编辑：

如果您希望日期采用特定格式，则可以使用如下所示CONVERT。

declare @maxNoOfDay int = (select max(noofdays) from @table)
;with seq( number ) as
(
    Select 0 as number
        union all
    Select number + 1
        from seq
        where number <= @maxNoOfDay
)
select number+1 as leaveid, 
       convert(varchar, Dateadd(day, number, fromdate), 103)  fromdate, 
       convert(varchar, Dateadd(day, number, fromdate), 103) todays 
from   @table t 
       inner join seq c 
               on c.number < t.noofdays 

在线演示

输出

+---------+------------+------------+
| leaveid | fromdate   | todays     |
+---------+------------+------------+
| 1       | 01/01/2019 | 01/01/2019 |
+---------+------------+------------+
| 2       | 02/01/2019 | 02/01/2019 |
+---------+------------+------------+