时间戳

首页 > 解决方案 > 将日期范围从单行拆分为多行显示的月份

sql - 将日期范围从单行拆分为多行显示的月份

问题描述

我有一个包含几百万个条目的表，所以我需要一个可以在一堆不同场景下工作的解决方案，因为我发现的大多数解决方案最多只能用于几次迭代。

我有一个 PERIOD_FROM 和一个 PERIOD_TO 状态检查，也就是帐户处于这种状态的时间。分开两个月的差额是没有问题的，但相当多的条目是用于预付款的帐户，因此它们的状态最多不会改变 2 年。

简化后的原始表格如下所示：

ID          PERIOD_FROM PERIOD_TO   DAYS
---------------------------------------------------------
115052454   02/04/2019  01/04/2021  730
115678935   06/04/2021  05/04/2023  729
119040627   06/04/2021  05/04/2023  729
115005487   01/04/2019  29/03/2021  728
116414279   17/04/2019  09/04/2021  723
116411046   17/04/2019  04/04/2021  718
116693271   24/04/2019  04/04/2021  711
123935704   05/07/2019  29/05/2021  694
119040627   16/05/2019  05/04/2021  690
115976183   02/05/2019  01/03/2021  669

我为两个月的差异解决方案所做的是创建一个 concat，如下所示：

CASE WHEN CONCAT(DATEPART(YYYY, [PERIOD_FROM]), FORMAT([PERIOD_FROM], 'MM')) = CONCAT(DATEPART(YYYY, [PERIOD_TO]), FORMAT([PERIOD_TO], 'MM'))
    THEN CONCAT(DATEPART(YYYY, [PERIOD_FROM]), FORMAT([PERIOD_FROM], 'MM'))
        ELSE CONCAT(DATEPART(YYYY, [PERIOD_FROM]), FORMAT([PERIOD_FROM], 'MM'), ',', DATEPART(YYYY, [PERIOD_TO]), FORMAT([PERIOD_TO], 'MM'))
END AS Period_Of

使用带有 string_split 的交叉应用，我可以将行分成两部分，并使用另一个 case 语句，我可以根据从/到的时间段是否等于或大于拆分值来分配完整值，但这仅适用于两个月的差异我需要长达 48 个月。

我需要一个看起来像这样的输出：

ID          PERIOD_FROM PERIOD_TO   DAYS
------------------------------------------------------
1150524545  02/04/2019  30/04/2019  730
1150524545  01/05/2019  31/05/2019  730
1150524545  01/06/2019  30/06/2019  730
1150524545  01/07/2019  31/07/2019  730
   …………            …………             …………    ……
1150524545  01/02/2021  28/02/2021  730
1150524545  01/03/2021  31/03/2021  730
1150524545  01/04/2021  01/04/2021  730

任何帮助将不胜感激！

更新：

感谢 Serg 的帮助，我有了一个解决方案。我稍微调整了一下，以便 Period_To 在最后一个日期结束，但没有在我的请求中指定，因为这不是主要问题。

标签： sqlsql-serverdateiteration

解决方案

使用数字表

-- Generate table of 1000 numbers starting 0
with t0(n) as (
 select n 
 from (
    values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
    ) t(n)
),nmbs as(
   select row_number() over(order by t1.n) - 1 n
   from t0 t1, t0 t2, t0 t3
)
--
select Id
  , dateadd(mm, nmbs.n, tbl.PERIOD_FROM) PERIOD_FROM
  , case when ys.NextDate > tbl.PERIOD_TO then tbl.PERIOD_TO else ys.NextDate end PERIOD_TO
  , DAYS
  , tbl.PERIOD_TO originalPERIOD_TO
from [my table] tbl
join nmbs
on dateadd(mm, nmbs.n, tbl.PERIOD_FROM) <= tbl.PERIOD_TO
cross apply (select  dateadd(mm, nmbs.n + 1, tbl.PERIOD_FROM) NextDate) ys
order by ID, dateadd(mm, nmbs.n, tbl.PERIOD_FROM);

小提琴

推荐阅读