sql - SQL Active Price Date From with Overlapping data range 折扣

Question

您好，我在编写查询时遇到问题。我需要用有效的 [DateFrom] 日期编写当前价格。如果折扣与数据范围重叠，我需要写折扣 [DateFrom] 如果折扣在下一次价格变化之前结束，则为价格

源数据

DateFrom         DateTo         Price       Type
--------------- --------------- ----------- ------
2019-05-25      2019-12-31      1000        Price
2019-05-26      2019-08-31      800         Discount
2020-01-01      2020-12-31      1100        Price
2020-07-05      2020-09-30      900         Discount

期望的结果

DateFrom        Price       Type
--------------- ----------- ------
2019-05-25      1000        Price
2019-05-26      800         Discount
2019-09-01      1000        Price -- back go original price
2020-01-01      1100        Price
2020-07-05      900         Discount
2019-10-01      1000        Price -- back go original price

而我目前的查询

declare @Day date
declare @MinDate date
declare @MaxDate date

declare @Result table (
id int PRIMARY KEY IDENTITY(1,1),
DateFrom date,
Price decimal(18,2),
Type varchar(10) ) 

IF OBJECT_ID('tempdb..#temp', 'U') IS NOT NULL DROP TABLE #temp


select 
ROW_NUMBER() OVER (ORDER BY DateFrom, PriceType Desc /*1st discount*/) as RowNumber,
cast(DateFrom as date) as DateFrom,
cast(DateTo as date) as DateTo,
--LEAD(DateFrom, 1) OVER (ORDER BY DateFrom) as NextDate1,
LAG(DateTo, 1) OVER (ORDER BY DateFrom) as PrevDate2,
--DATEDIFF(dd, DateTo, LEAD(DateFrom, 1) OVER (ORDER BY DateFrom)) as NextDate1Diff,
--LEAD(DateTo, 1) OVER (ORDER BY DateFrom) as NextDate2,
CASE WHEN DateTo>LEAD(DateFrom, 1) OVER (ORDER BY DateFrom) THEN 'yes' ELSE 'no' END as overlaped,
CASE WHEN PriceType=568 THEN 'Price'
    WHEN PriceType=3146 THEN 'Discount' END as Type,
--LEAD(CASE WHEN PriceType=568 THEN 'Price'
    --WHEN PriceType=3146 THEN 'Discount' END, 1) OVER (ORDER BY DateFrom) as NextType,
LAG(CASE WHEN PriceType=568 THEN 'Price'
WHEN PriceType=3146 THEN 'Discount' END, 1) OVER (ORDER BY DateFrom) as PrevType,
LAG(DecimalColumn3) OVER (ORDER BY DateFrom) as PrevPrice,

H_DecimalColumn1, --as [Dicount value],
DecimalColumn3 as Price 
INTO #temp
from Table where PriceType in (568,3146)
order by DateFrom

select * from #temp

select @MinDate=MIN(DateFrom), @Day=MIN(DateFrom) from #temp
select @MaxDate=MAX(DateTo) from #temp 

--Select @MinDate, @MaxDate,@Day
--select DATEDIFF(dd, DateTo, LEAD(DateFrom, 1) OVER (ORDER BY DateFrom)) as NextDate2Diff,
--* from #temp where Type='Discount'
--select * from #temp where Type='Price'

WHILE @Day<@MaxDate
BEGIN

if exists(select * from #temp where DateFrom=@Day or DateTo=@Day)
    begin

    if exists(select * from #temp where @Day=DateFrom)
        BEGIN
            select 'D1-T', @Day, overlaped, 
            DATEADD(dd, 1,PrevDate2) as PrevDate1More, CASE WHEN Type='Price' and overlaped='yes' and PrevType='Discount' THEN ISNULL(DATEADD(dd, 1,PrevDate2),DateFrom) ELSE DateFrom END as DayG, 
            CASE WHEN Type='Price' and overlaped='yes' and PrevType='Discount' THEN ISNULL(PrevPrice,Price) ELSE Price END as Price
            ,DateFrom, DateTo
            from #temp where @Day=DateFrom /*or @Day=DateTo*/ 
            ORDER BY DateFrom, Type Desc

            INSERT INTO @Result(Od, Price, Type)
            select  CASE WHEN Type='Price' and overlaped='yes' and PrevType='Discount' THEN ISNULL(DATEADD(dd, 1,PrevDate2),DateFrom) ELSE DateFrom END as DayG, 
            Price, Type from #temp where @Day=DateFrom /*or @Day=DateTo*/ ORDER BY DateFrom, Type Desc
        END
    else /* @Day=DateTo */
        BEGIN
            --IF Discount
            select 'D2-FR', @Day, overlaped, CASE WHEN Type='Discount' THEN DATEADD(dd, 1,DateTo) ELSE DateTo END as day, PrevPrice, * 
            from #temp where DateTo=@Day and Type='Discount'

            INSERT INTO @Result(Od, Price, Type)
            select CASE WHEN Type='Discount' THEN DATEADD(dd, 1,DateTo) ELSE DateTo END as day, 
            PrevPrice, PrevType 
            from #temp where DateTo=@Day and Type='Discount'

            --IF Price
            /*
            select 'D2-FC', @Day, overlaped, CASE WHEN Type='Price' THEN DATEADD(dd, 1,DateTo) ELSE DateTo END as day, PrevPrice, * 
            from #temp where DateTo=@Day and Type='Price'

            INSERT INTO @Result(Od, Price, Type)
            select CASE WHEN Type='Discount' THEN DATEADD(dd, 1,DateTo) ELSE DateTo END as day, 
            Price, Type
            from #temp where DateTo=@Day and Type='Price'
            */
        END
    end

    SELECT @Day=DATEADD(dd, 1, @Day) -- increment
END

select distinct Od, Price, Type, ROW_NUMBER() OVER (PARTITION BY Od ORDER BY Od, Type DESC) as Rank from @Result

;WITH CTE AS
(
select distinct Od, Price, Type, ROW_NUMBER() OVER (PARTITION BY Od ORDER BY Od, Type DESC) as Rank from @Result
)
SELECT * FROM CTE WHERE Rank=1

标签： sqlsql-server

Answer 1

这很棘手。我认为最好的解决方案是取消透视数据，然后使用逻辑和窗口函数对其进行排列。

假设您总是有全价，那么这应该可行：

select v.dte as datefrom, dateadd(day, -1, lead(v.dte) over (order by v.dte)) as dateto, v.price, v.type
from t join
     t tfull
     on tfull.type = 'Price' and
        tfull.datefrom <= dateadd(day, 1, t.dateto) and
        tfull.dateto >= dateadd(day, 1, t.dateto)  cross apply
     (values (t.datefrom, t.price, t.type),
             (dateadd(day, 1, t.dateto), tfull.price, tfull.type)
     ) v(dte, price, type)
order by dte

这是一个 db<>fiddle。

编辑：

当折扣和全价在同一日期结束时，上述版本有一些不必要的行。唉。这解决了这个问题：

select t.*
from (select v.dte as datefrom, dateadd(day, -1, lead(v.dte) over (order by v.dte)) as dateto, v.price, v.type
      from t join
           t tfull
           on tfull.type = 'Price' and
              tfull.datefrom <= dateadd(day, 1, t.dateto) and
              tfull.dateto >= dateadd(day, 1, t.dateto)  cross apply
           (values (t.datefrom, t.price, t.type),
                   (dateadd(day, 1, t.dateto), tfull.price, tfull.type)
           ) v(dte, price, type)
     ) t
where dateto >= datefrom
order by dateto;

还有db<>fiddle。