基于数据范围生成额外行的更高效SQL实现方案问询

现有产品数据存储了产品的整体有效期和冻结时段，需要按规则拆分出不同状态的记录。原表结构及测试数据如下：

CREATE TABLE #Products(
    [ProductId] [int] NULL,
    [Product] [nvarchar](255) NULL,
    [Startdate] [date] NULL,
    [Enddate] [date] NULL,
    [Startdate_blocked] [date] NULL,
    [Enddate_blocked] [date] NULL
) 

INSERT INTO #Products(
[ProductId] ,
[Product] ,
[Startdate] ,
[Enddate] ,
[Startdate_blocked] ,
[Enddate_blocked]
)
VALUES('33',    'PRODUCTNUMBER33',  '2010-01-01',   NULL,   '2018-10-01',   NULL)
     ,('36',    'PRODUCTNUMBER36',  '2010-01-01',   NULL,   '2018-11-01',   NULL)
     ,('58',    'PRODUCTNUMBER58',  '2010-01-01',   NULL,   '2018-10-01',   '2020-10-30')
     ,('75',    'PRODUCTNUMBER75',  '2010-01-01',   NULL,   '2020-01-01',   '2020-07-07')
     ,('80',    'PRODUCTNUMBER80',  '2010-01-01',   '2015-08-31',   NULL,   NULL)

拆分规则 #

• 产品整体有效期的valid_till：若原Enddate为NULL，替换为'2999-12-31'表示永久有效
• 当Startdate_blocked不为空时：
  • 生成frozen状态记录：valid_from = Startdate_blocked，valid_till = 若Enddate_blocked为NULL则用'2999-12-31'，否则用Enddate_blocked
  • 生成active状态记录：valid_from = Startdate，valid_till = Startdate_blocked
• 当Startdate_blocked为空时：
  • 仅生成active状态记录：valid_from = Startdate，valid_till = 处理后的原Enddate

简洁实现方案（替代重复UNION） #

使用CROSS APPLY配合值列表动态生成所需记录，避免重复编写字段逻辑：

SELECT 
    p.ProductId,
    p.Product,
    s.Status,
    s.Valid_From,
    -- 统一处理有效期的NULL情况
    COALESCE(s.Valid_Till, '2999-12-31') AS Valid_Till
FROM #Products p
CROSS APPLY (
    -- 生成active状态记录
    SELECT 
        'active' AS Status,
        p.Startdate AS Valid_From,
        CASE WHEN p.Startdate_blocked IS NOT NULL THEN p.Startdate_blocked ELSE p.Enddate END AS Valid_Till
    UNION ALL
    -- 仅当存在冻结时段时生成frozen记录
    SELECT 
        'frozen' AS Status,
        p.Startdate_blocked AS Valid_From,
        p.Enddate_blocked AS Valid_Till
    WHERE p.Startdate_blocked IS NOT NULL
) s
ORDER BY p.ProductId, s.Status DESC;

预期输出 #

内容的提问来源于stack exchange，提问作者Eseosa Omoregie