I have a table with prices per ExternalID, TestCenter, Pricetype, date and Timeslot, that looks like below with prices per timeslot.

What I am trying to do is to combine the timeslots so that the end time is the time when the price changes. So instead of writing out every timeslot between 06.00 to 07.00 I would just write a Start Time and End Time for when that price is valid.

What changes is the ExternalID, TestCenterID, Pricetypei, dt_date and ofcourse the timeslot.

I've sorted the list in the right order and added the End Time for each timeslot. but unfortunately I cant figure out how to loop and aggregate it as per the second table.

What I thought of doing is to create a table with the starting sku and the end time.

Create table #SKUEND
(
    SKU varchar (25),
    ENDTIME time,
    iter int,
    row_num int,
);


DECLARE @Prices int;
DECLARE @row1 INT=1;
DECLARE @ITER INT=1;
DECLARE @SKU varchar(25);
DECLARE @ENDTIME time;
DECLARE @DT as date;
select @Prices=count(*) from #test

while @row1<@Prices

BEGIN

IF @ITER=1
begin
    SET @SKU=(select top 1 sku from #test where row_num=@row1)
    SET @DT=(select top 1 dt_date from #test where row_num=@row1)
    end

IF (SELECT TOP 1 TIMEPRICE from #test where row_num=@row1)<>(SELECT TOP 1 NextPrice from #test where row_num=@row1) 
BEGIN
IF (SELECT TOP 1 dt_date from #test where row_num=@row1)<>@DT
SET @ENDTIME='23:59:59'
else
SET @ENDTIME=(select top 1 EndTime from #test where row_num=@row1)
;

INSERT into #SKUEND 
VALUES (@SKU,@ENDTIME,@ITER,@row1)
SET @ITER=0

END


set @row1 =1
set @ITER =1
END

That gives me the result:

The issue I am having now is that when ExternalID, TestCenteriD, Pricetypeid or dt_date changes, then it doesnt really start over. So I think I need a loop over those columns.

CodePudding user response：

Try this query:

WITH CTE1 AS
(
SELECT *,
CASE WHEN LAG(TimePrice,1,0.00) OVER(PARTITION BY ExternalID,TestCenterID,pricetypeid,dt_date  ORDER BY timeslot) = TimePrice THEN 0 ELSE 1 END AS flag
FROM Table1
)
SELECT ct1.SKU,ct1.ExternalID,ct1.TestCenterID,
ct1.pricetypeid,ct1.timeslot,ct1.dt_date,
ct1.TimePrice,
LEAD(timeslot,1,'23:59:00.0000000') OVER(PARTITION BY ExternalID,TestCenterID,pricetypeid,dt_date  ORDER BY timeslot) as EndTime
FROM CTE1 ct1
WHERE flag = 1;

db fiddle link