基于具有多个 dateFrom 和 DateTo 列的单独表构建时间线？-解网

问：

我有两个表，tblPrice 和 tblDiscount，每个表都有其值，带有 liveFrom 和 liveUntil 列（以及其他一些列，但我将它们省略以尝试使其更简单）。

我试图做的是将价格和折扣列表显示为有序列表，该列表将显示价格如何根据价格变化或折扣变化而变化。

tbl价格

排	价格ID	retailPrice	live从	LiveUntil
1	446413	1666.33	2022-01-31 11:36:21.490	2022-04-08 15:13:41.230
2	1338193	1666.33	2022-04-09 09:30:14.043	2023-04-05 09:37:21.767
3	2707357	1749.65	2023-04-05 09:37:21.767	零

tbl折扣

排	日志 ID	折扣	live从	LiveUntil
1	192	0.3700	2022-01-31 11:27:45.060	2023-01-09 14:32:24.413
2	498	0.3200	2023-01-09 14:32:24.413	2023-04-11 15:40:06.460
3	639	0.3100	2023-04-11 15:40:06.460	零

预期结果

排	retailPrice	折扣	live从	生活直到
1	1666.33	0.37	31/01/2022 11:36	08/04/2022 15:13
2	1666.33	0.37	09/04/2022 09:30	09/01/2023 14:32
3	1666.33	0.32	09/01/2023 14:32	05/04/2023 09:37
4	1749.65	0.32	05/04/2023 09:37	11/04/2023 15:40
5	1749.65	0.317	11/04/2023 15:40	零

需要注意的事项是：

价格不一定是连续运行的（请注意，tblPrice 上的第 1 行和第 2 行在价格上没有区别，它只是碰巧结束，然后在以后重新开始，所以这将给出一个产品没有有效价格的时间段）。
我们关注的是价格，但折扣可以是实时的，而价格不是这样，在这种情况下，有效的实时开始日期仍然需要是价格的日期，而不是折扣。
价格可以在没有折扣的情况下处于活动状态，因此有效折扣将为 0

我尝试根据日期范围将表格相互连接，然后使用 case 语句来显示 live from 和 live until 日期，但这似乎不对，我只是不太明白逻辑。结果并没有像我希望的那样出现，我觉得我在错误的方向上掉进了一个兔子洞。

到目前为止，我的查询（如前所述，我不确定是否是正确的方法）是：

SELECT
    P.[priceID],
    P.[retailPrice],
    P.[liveFrom],
    P.[liveUntil],
    D.[logID],
    D.[discount],
    D.[liveFrom],
    D.[liveUntil],
    ------------
    CASE
        WHEN SDV.[liveFrom] <= P.[liveFrom] AND (SDV.[liveUntil] >= P.[liveFrom] OR SDV.[liveUntil] IS NULL) THEN
            SDV.[liveFrom]
        WHEN SDV.[liveFrom] >= P.[liveFrom] AND (SDV.[liveUntil] <= P.[liveUntil] OR P.[liveUntil] IS NULL) THEN
            P.[liveFrom]
        ELSE
            '1900-01-01 00:00:00'
    END AS [EFFECTIVE_FROM],
    CASE
        WHEN ISNULL(P.[liveUntil],@date) < ISNULL(SDV.[liveUntil],@date) THEN
            P.[liveUntil]
        ELSE
            SDV.[liveUntil]
    END AS [EFFECTIVE_UNTIL]
FROM 
    tblPrice P
    INNER JOIN tblDiscount D ON P.[supplierDiscountID] = D.[supplierDiscountID]
        AND ((
                D.[liveFrom] <= P.[liveFrom] 
                AND (D.[liveUntil] >= P.[liveFrom] OR D.[liveUntil] IS NULL) 
            ) OR
            (
                D.[liveFrom] >= P.[liveFrom]
                AND (D.[liveFrom] <= P.[liveUntil] OR P.[liveUntil] IS NULL)
            ))
ORDER BY
    P.[liveFrom],
    D.[liveFrom];

我没有得到我想要的结果，而且我要走的路线似乎不对。谁能给我任何指示或想法？

sql sql-server datetime gaps-and-islands 时间线

-- Sample data
declare @tblPrice table
    ( priceID int not null, retailPrice money not null
    , liveFrom datetime not null, liveUntil datetime)
declare @tblDiscount table
    ( logID int not null, discount decimal(5,4) not null
    , liveFrom datetime not null, liveUntil datetime)
insert into @tblPrice values
     ( 446413,1666.33,'2022-01-31T11:36:21.490','2022-04-08T15:13:41.230')
    ,(1338193,1666.33,'2022-04-09T09:30:14.043','2023-04-05T09:37:21.767')
    ,(2707357,1749.65,'2023-04-05T09:37:21.767', null)
insert into @tblDiscount values
     (192, 0.37, '2022-01-31T11:27:45.060','2023-01-09T14:32:24.413')
    ,(498, 0.32, '2023-01-09T14:32:24.413','2023-04-11T15:40:06.460')
    ,(639, 0.317,'2023-04-11T15:40:06.460', null)

-- Prices with discounts available or without any discount
select P.retailPrice
    , isNull(D.discount, 0) as discount
    , case when P.liveFrom  >= isNull(D.liveFrom, P.liveFrom)
        then P.liveFrom
        else D.liveFrom
      end as liveFrom
    , case when P.liveUntil <= isNull(D.liveUntil, P.liveUntil)
        then P.liveUntil
        else D.liveUntil
      end as liveUntil
from @tblPrice P
left join @tblDiscount D on
     -- P.supplierDiscountID = D.supplierDiscountID and
     D.liveFrom <= isNull(P.liveUntil, getDate())
 and P.liveFrom <= isNull(D.liveUntil, getDate())

union all
-- Prices that begins before 1st discount available
select x.retailPrice
    , 0 as discount
    , x.liveFrom
    , x.lf as liveUntil
from (
select P.retailPrice, P.liveFrom
    , min(D.liveFrom) lf
from @tblPrice P
left join @tblDiscount D on
     -- P.supplierDiscountID = D.supplierDiscountID and
     D.liveFrom <= isNull(P.liveUntil, getDate())
 and P.liveFrom <= isNull(D.liveUntil, getDate())
group by P.priceID, P.retailPrice, P.liveFrom
having min(D.liveFrom)
     > P.liveFrom
) x

union all
-- Prices that ends after last discount available
select x.retailPrice
    , 0 as discount
    , x.lu as liveFrom
    , x.liveUntil
from (
select P.retailPrice, P.liveUntil
    , max(isNull(D.liveUntil, getDate())) lu
from @tblPrice P
left join @tblDiscount D on
     -- P.supplierDiscountID = D.supplierDiscountID and
     D.liveFrom <= isNull(P.liveUntil, getDate())
 and P.liveFrom <= isNull(D.liveUntil, getDate())
group by P.priceID, P.retailPrice, P.liveUntil
having max(isNull(D.liveUntil, getDate()))
     < isNull(P.liveUntil, getDate())
) x

order by liveFrom

基于具有多个 dateFrom 和 DateTo 列的单独表构建时间线？

Build a timeline based on separate tables with multiple dateFrom and DateTo columns?

评论

评论