将日期范围从单行拆分为多行显示的月份 [英] Split date range from a single row into months that are displayed in multiple rows
问题描述
我的表有几百万个条目，所以我需要一个可以在多种情况下工作的解决方案，因为我发现的大多数解决方案最多只能进行几次迭代.
I have a table with a few million entries, so I need a solution that can work on a bunch of different scenarios as most solutions I have found is only for a few iterations at best.
我有一个PERIOD_FROM和PERIOD_TO状态检查，也就是该帐户已处于这种状态多长时间了.划分两个月的差额是没有问题的，但是相当多的条目是针对已预付款的帐户的，因此其状态最多可以更改2年.
I have a PERIOD_FROM and a PERIOD_TO status check, aka how long the account has been in this state. It is no problem to split a two month difference but quite a few of the entries are for accounts that have paid in advance so their status will not change for up to 2 years.
简化后的原始表格如下所示:
The original table simplified looks something like this:
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，如下所示:
What I have done for the two month difference solution is create a concat as follows:
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语句，我可以基于from/to的周期等于或大于分割值来分配完整值，但这仅适用于两个月的差额，我最多需要48个月.
Using cross apply with string_split I can then split the line into two and using another case statement I can assign the full values based on if the period from/to is equal or greater than the split value, but this only works for a two month difference and I need up to 48 months.
我需要一个看起来像这样的输出:
I require an output that would look something like this:
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在最后一个日期结束，但由于不是主要问题，因此未在我的请求中指定.
Thanks to the help of Serg, I have a solution. I tweaked it slightly so that the Period_To ends on the last date but had not specified that in my request as it was not a main concern.
推荐答案
使用数字表
-- 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);
这篇关于将日期范围从单行拆分为多行显示的月份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
