基于时态表OrderlinesHistory的Ecode状态变动月度汇总SQL需求

环境说明 #

我们使用的#OrderlinesHistory是一张时态表，以下是临时表结构及测试数据：

drop table #Orderlines
drop table #Orders
drop table #OrderlinesHistory

create table #Orders
(Id uniqueidentifier
,OrderDate datetime
,TotalValue Decimal(9,2)
)
create table #Orderlines
(Id uniqueidentifier
,OrderId uniqueidentifier
,ActivationDate dateTime
,Value decimal (9,2)
,Ecode varchar(50)
,EcodeStatusId int
,ValidFrom dateTime
,ValidTo DateTime)

create Table #OrderlinesHistory
(Id uniqueidentifier
,OrderId uniqueidentifier
,ActivationDate dateTime
,Value decimal (9,2)
,Ecode varchar(50)
,EcodeStatusId int
,ValidFrom dateTime
,ValidTo DateTime)


insert #Orders
(id,OrderDate,TotalValue)
values ('43D7E9F9-7688-4905-BC69-08DCF81D15BB','2024-10-26',40)
,('3D24980B-B83F-4D8B-DE13-08DCF81D15AD','2024-11-25',25)

insert #Orderlines
(id,OrderId,ActivationDate,Ecode,EcodeStatusId,ValidFrom,ValidTo,Value)
values
('2987bdba-5345-4154-b8bd-41bc0186eefc','43D7E9F9-7688-4905-BC69-08DCF81D15BB','2024-10-26','ECODE-0',1, '2024-10-26',null,10)
,('2FAC6F53-D6E0-4C99-8C29-08DCD62AB512','43D7E9F9-7688-4905-BC69-08DCF81D15BB','2024-11-01','ECODE-1',2, '2024-11-05',null,10)
,('A5776A49-9A00-4450-8C2B-08DCD62AB512','43D7E9F9-7688-4905-BC69-08DCF81D15BB','2024-11-01','ECODE-2',3, '2024-11-17',null,10)
,('05B58778-94BA-4B85-8C27-08DCD62AB512','43D7E9F9-7688-4905-BC69-08DCF81D15BB','2024-11-01','ECODE-3',4, '2024-11-30',null,10)
,('F3639DD8-3515-47EC-2A3B-08DCD97CAC6C','3D24980B-B83F-4D8B-DE13-08DCF81D15AD','2024-11-25','ECODE-1-A',1, '2024-11-25',null,25)

insert #OrderlinesHistory
(id,OrderId,ActivationDate,Ecode,EcodeStatusId,ValidFrom,ValidTo,Value)
values
('2FAC6F53-D6E0-4C99-8C29-08DCD62AB512','43D7E9F9-7688-4905-BC69-08DCF81D15BB','2024-10-26',null,null, '2024-10-26','2024-10-28', 10)
,('2FAC6F53-D6E0-4C99-8C29-08DCD62AB512','43D7E9F9-7688-4905-BC69-08DCF81D15BB','2024-11-01','ECODE-1',1, '2024-10-28','2024-11-05',10)

,('A5776A49-9A00-4450-8C2B-08DCD62AB512','43D7E9F9-7688-4905-BC69-08DCF81D15BB','2024-10-26',null,null, '2024-10-26','2024-10-28',10)
,('A5776A49-9A00-4450-8C2B-08DCD62AB512','43D7E9F9-7688-4905-BC69-08DCF81D15BB','2024-11-01','ECODE-2',1, '2024-10-28','2024-11-17',10)

,('05B58778-94BA-4B85-8C27-08DCD62AB512','43D7E9F9-7688-4905-BC69-08DCF81D15BB','2024-10-26',null,null, '2024-10-26','2024-10-28',10)
,('05B58778-94BA-4B85-8C27-08DCD62AB512','43D7E9F9-7688-4905-BC69-08DCF81D15BB','2024-11-01','ECODE-3',1, '2024-10-28','2024-11-30',10)

需求说明 #

需按Ecode状态变动的月份汇总其状态情况：

• 10月下单的4个Ecode在10月状态均为1（生效），其中3个分别在11月不同日期变更为状态2（已兑换）、3（已取消）、4（已过期），剩余1个保持生效状态
• 11月新增1个25英镑的Ecode，状态为1（生效）
  需生成符合目标样式的汇总报表。

尝试的SQL语句 #

-- 按订单月份和状态汇总数据
WITH OrderMonths AS (
    SELECT
        FORMAT(o.OrderDate, 'MMM-yy') AS OrderMonth,
        o.TotalValue AS OrderTotal,
        h.EcodeStatusId,
        h.Value
    FROM
        #Orders o
    JOIN
        #Orderlines ol ON o.Id = ol.OrderId
    JOIN
        #OrderlinesHistory h ON ol.Id = h.Id
    WHERE
        h.ValidFrom <= o.OrderDate
        AND (h.ValidTo IS NULL OR h.ValidTo >= o.OrderDate)
),
StatusSummary AS (
    SELECT
        OrderMonth,
        SUM(CASE WHEN EcodeStatusId = 1 THEN Value ELSE 0 END) AS Live,
        SUM(CASE WHEN EcodeStatusId = 2 THEN Value ELSE 0 END) AS Redeemed,
        SUM(CASE WHEN EcodeStatusId = 3 THEN Value ELSE 0 END) AS cancelled
    FROM
        OrderMonths
    GROUP BY
        OrderMonth
)
SELECT
    sm.OrderMonth,
    SUM(om.OrderTotal) AS OrderTotal,
    sm.Live,
    sm.Redeemed,
    sm.cancelled
FROM
    StatusSummary sm
LEFT JOIN
    (SELECT FORMAT(OrderDate, 'MMM-yy') AS OrderMonth, TotalValue AS OrderTotal FROM #Orders) om
    ON sm.OrderMonth = om.OrderMonth
GROUP BY
    sm.OrderMonth, sm.Live, sm.Redeemed, sm.cancelled;

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