CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2016
Rawdon Hume
Rawdon Hume

Reputation: 145

ms sql temporal table find history on column where last changed

I need a query on temperal table's history table, to find the last time a column value changed and what the difference was vs now. As there are other changes that are also recorded in the table I need to find only the change where to column data changed, then find out by how much and when it changed

my temperal history table looks like this

Id     |Price          |LastModifiedDate  |other data
------ |---------------|------------------|--------
696733 |9995           |08/Nov/2016 09:30 |1 -other change
696733 |9995           |06/Nov/2016 09:28 |2 -price change -current price
696733 |10995          |30/Oct/2016 09:29 |2 -other change - prev price
696733 |10995          |29/Oct/2016 09:29 |3 -other change
696733 |10995          |26/Oct/2016 10:10 |4 -other change
696733 |10995          |26/Oct/2016 08:42 |5 -other change
696733 |10995          |25/Oct/2016 10:11 |6 -price change -
696733 |11595          |22/Oct/2016 09:50 |6 -other change - old old price
696733 |11595          |21/Oct/2016 15:26 |7 -other change

So id be looking to return 9995 as current price and 10995 as the previous price and the date of change as 06/Nov/2016 09:28 and ignore any other previous price changes. I will need to then filter on the results to check if the change was say 28 days ago so it need to be a solution I can put into a sub query or cross apply

I been trying to use "OVER(order" by to avoid using lost of nested queries

thanks in advance

Edit

My sql fiddle example http://sqlfiddle.com/#!6/05db1/7

note sql fiddle doesn't support temperal tables so had to mimic it a bit

Upvotes: 2

Views: 2571

Answers (1)

jtate
jtate

Reputation: 2706

You can use an OUTER APPLY with an ORDER BY to get the most recent history record that was a different price than the current price (assuming the current price is in the Pricing table).

SELECT
    p.Id,
    p.Price AS current_price,
    ph.Price AS prev_price,
    ph.LastModifiedDate AS prev_price_date
FROM [Pricing] p
    OUTER APPLY (
        SELECT TOP 1 ph1.Price, ph1.LastModifiedDate
        FROM [PriceHistory] ph1
        WHERE ph1.Id = p.Id
            AND ph1.Price <> p.Price
        ORDER BY ph1.LastModifiedDate DESC
    ) AS ph

Upvotes: 2

Related Questions