SQL Active Price Date From with Overlapping data range Discounts

Question

Hello I have problem with writing query. I need to write current prices with valid [DateFrom] date's. If discount overlaps data range I need to write discount [DateFrom] and It's price if discount is ended before next price change I need to return to previous price with day+1 DateFrom

Source data

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

Desired result

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

And my current query

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

Answer 1

Provided Discounts do not go in pairs but only after the 'Price'

-- test data
with tbl as (
  select DateFrom, DateTo, Price, Type
  from (
    values 
     ( cast('2019-05-25' as date),cast('2019-12-31' as date),1000,'Price')
    ,( cast('2019-05-26' as date),cast('2019-08-31' as date),800 ,'Discount')
    ,( cast('2020-01-01' as date),cast('2020-12-31' as date),1100,'Price')
    ,( cast('2020-07-05' as date),cast('2020-09-30' as date),900 ,'Discount')
    ) tbl (DateFrom, DateTo, Price, Type)
)

-- the query
select t2.DateFrom, t2.DateTo, t2.Price, t2.Type
from (
    select DateFrom, DateTo, Price, Type
      , lag(DateTo) over (order by DateFrom) prevTo
      , lag(Price) over (order by DateFrom) prevPrice
    from  tbl
 ) t
 cross apply(
    select  DateFrom, DateTo, Price, Type
    union
    select DATEADD(dd,1, DateTo) DateFrom, prevTo DateTo, prevPrice Price, 'back' type
    where t.Type = 'Discount' and t.prevTo >  t.DateTo
 ) t2;

EDIT Corrected DateTo logic.

Answer 2

This is tricky. I think the best solution is to unpivot the data and then arrange it using logic and window functions.

Assuming that you always have a full price, then this should work:

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

Here is a db<>fiddle.

EDIT:

The above version has some unnecessary rows when the discount and full price end on the same date. Alas. This fixes that problem:

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;

And the db<>fiddle.