I'm trying to creare a SQL Server query (without LOOPING) for the following:

I need to get latest inserted [Price] and [PriceDate] from a table [Prices] but with the following condition> if the price "before the latest", and "the latest" are the same, return the "before the latest" until the previous price is not the same anymore.

Example:

So for this example, it should return ID 4, PriceDate 5/31/2022. The latest entry is 8/31/2022, but since the price before (7/31/2022) is 1.89 I need to get this one, and since the one before (6/30/2022) is still 1.89, I need to keep looking back, until the previous entry (PriceDate) has a different price (4/30/2022).

Note that for 1/3/2022 the price is 1.89 again, however I don't care about this date since it belong to a different sequential group.

I would do it with a loop, but since this table has thousands of thousands of records for different AssetIDs, I didn't want to take this approach.

This what I got so far, but it not returning the right date by AssetID.

SELECT AssetID, price, PRICEDATE,
     FIRST_VALUE(PriceDate) OVER (
        PARTITION BY Price ORDER BY PriceDate 
        ) AS LastValue
FROM PricesSELECT AssetID, price, PRICEDATE,
     FIRST_VALUE(PriceDate) OVER (
        PARTITION BY Price ORDER BY PriceDate 
        ) AS LastValue
FROM Prices

Thanks for the help

CodePudding user response：

This will return id 4 and 8, because those are the id's where a price change did occur.

select *
from (
   SELECT 
      id, 
      PriceDate, 
      price, 
      lead(price) over (order by id) previousprice
   from prices 
) x
where price<>previousprice

see: DBFIDDLE

output:

CodePudding user response：

You'll want them separated by AssetID too.

DROP TABLE IF EXISTS #P;

CREATE TABLE #P
(ID INT IDENTITY PRIMARY KEY,
AssetID INT,
PriceDate DATE,
Price Money);

INSERT INTO #P
(
AssetID,
PriceDate,
Price)
VALUES
(
19969,
'2022-08-31',
1.89
),
(
19969,
'2022-07-31',
1.89
),
(
19969,
'2022-06-30',
1.89
),
(
19969,
'2022-05-31',
1.89
),
(
19969,
'2022-04-30',
1.0
),
(
19969,
'2022-03-31',
1.0
),
(
19969,
'2022-02-28',
1.0
),
(
19969,
'2022-01-31',
1.0
),
(
19969,
'2022-01-03',
1.89
),
(
19969,
'2021-12-31',
1.89
);

SELECT * FROM #P;

WITH cte AS
(
    SELECT 
        ID,
        AssetID,
        PriceDate,
        Price,
        LEAD(Price) OVER (PARTITION BY AssetID ORDER BY AssetID, PriceDate DESC) AS PreviousPrice
    FROM
        #P
)
SELECT * FROM cte 
WHERE PreviousPrice <> Price OR PreviousPrice IS NULL
ORDER BY PriceDate DESC;