Sql 如何查找所提供日期范围的所有季度开始日期和结束日期

发布于01月11日

这是我的实际数据

ID	StartDateTime	EndDateTime	Price
1	2024-01-01	2024-06-30	14.00
2	2024-07-01	2024-12-30	10.00

预期yields

ID	StartDateTime	EndDateTime	Price
1	2024-01-01	2024-03-31	14.00
1	2024-04-01	2024-06-30	14.00
2	2024-07-01	2024-09-30	10.00
2	2024-10-01	2024-12-30	10.00

我试过了，但这将从第二季度开始

with cte as (
      select id, start_date, num_quarters
      from t
      union all
      select id, dateadd(month, 3, start_date), num_quarters - 1
      from cte
      where num_quarters > 1
     )
select *
from cte;

推荐答案

在SQL Server 2022上，您可以使用DATETRUNC()、DATEDIFF()、GENERATE_SERIES()和DATEADD()的组合来生成季度数.然后可以应用函数LEAST()和GREATEST()来将第一个季度和最后一个季度修剪为原始范围(例如在您的'2024-12-30'结束日期的情况下).不需要CTE.

逻辑:

DATEDIFF()计算所需的季度数-1.如果原始日期范围在同一季度开始和结束，则季度差值将为零.
GENERATE_SERIES()会生成一个范围为0..N的数字.
DATETRUNC()用于计算计算季度开始日期和结束日期的基准日期.
DATEADD()然后计算这些日期.季度结束日期是通过加上一个额外的季度并减go 一天来计算的.
然后，可以应用LEAST()和GREATEST()将第一个季度和最后一个季度修剪到原始范围.

SELECT T.ID, D.StartDateTime, D.EndDateTime, T.Price
FROM Data T
CROSS APPLY GENERATE_SERIES(0, DATEDIFF(quarter, T.StartDateTime, T.EndDateTime)) S
CROSS APPLY (SELECT DATETRUNC(quarter, T.StartDateTime) AS QStart) Q
CROSS APPLY (
    SELECT
        GREATEST(
            DATEADD(quarter, S.value, Q.QStart),
            T.StartDateTime
            ) AS StartDateTime,
        LEAST(
            DATEADD(day, -1, DATEADD(quarter, S.value + 1, Q.QStart)),
            T.EndDateTime
            ) AS EndDateTime
) D
ORDER BY T.ID, D.StartDateTime

结果(包括一些额外的测试数据):

ID	StartDateTime	EndDateTime	Price
1	2024-01-01	2024-03-31	14.00
1	2024-04-01	2024-06-30	14.00
2	2024-07-01	2024-09-30	10.00
2	2024-10-01	2024-12-30	10.00
3	2023-11-15	2023-12-31	88.00
3	2024-01-01	2024-03-31	88.00
3	2024-04-01	2024-06-30	88.00
3	2024-07-01	2024-08-14	88.00
4	2024-07-15	2024-08-14	99.00