Query to show old and new values from another table - query the latest ChangeDate-CodePudding

I have 3 tables in SQL Server 2012.

Table History is the history of all the changes made to the values in Table A. It can have many changes done to PRICE, LOT, INTEREST, but in most cases the value is only changed once.

Table A

AID PRICE   LOT    INTEREST
------------------------
1   1500    9000   0.5
2   2500    20     1.5

Table B

BID AID
--------
11  1
22  2

Table History

BID     ChangeField     OldValue    NewValue   ChangeDate
------------------------------------------------------------
11      PRICE           1700        1500       1/1/22
11      LOT             10000       8000       12/15/21 
11      LOT             8000        9000       2/2/22

I need a query that shows Table A with the old and new values from Table History. If there are more than 1 changes, then for the Old value, get the most recent previous value. In the above example, the most recent previous value for Lot is 8000 from 2/2/22 (not 10000 from 12/15/21).

Example:

AID OldPRICE    NewPRICE    OldLot  NewLot  OldInterest NewInterest
----------------------------------------------------------------
1   1700        1500        8000    9000    0.5         0.5
2   2500        2500        20      20      1.5         1.5

This query returns OldLot = 10000 (the biggest value for Lot), instead of 8000 (the most recent previous value)

SELECT A.AID,
   MAX(CONVERT(numeric(30, 2),ISNULL(CASE WHEN h.ChangeField = 'LOT' THEN h.OldValue 
   END,A.LOT))) OldLot,
   MAX(CONVERT(numeric(30, 2),ISNULL(CASE WHEN h.ChangeField = 'LOT' THEN h.NewValue 
   END,A.LOT))) NewLot
FROM A 
LEFT JOIN B ON A.AID = B.AID
LEFT JOIN History h ON B.BID = h.BID
WHERE H.BID = 11
GROUP BY A.AID

That returns below (notice OldLot = 10000 instead of 8000)

AID OldLot  NewLot  
--------------------
1   10000    9000

Thank you

CodePudding user response：

If I understand your requirement correctly you are just after the most recent history value per field which you can do using a row_number partition, then you just need to pivot the rows to columns to combine with the current values:

with h as (
    select b.aid, h.*, 
      Row_Number() over(partition by h.bid, h.changefield order by h.changedate desc) rn
    from history h
    join b on b.bid=h.bid
)
select a.aid,
  isnull(h.oldprice, a.price) OldPrice, a.PRICE newprice,
  isnull(h.oldLot, a.lot) OldLot, a.Lot NewLot,
  isnull(h.oldInterest, a.interest) OldInterest, a.Interest newInterest
from a
outer apply (
  select 
    max(case when changefield = 'LOT' then oldvalue end) OldLot,
    max(case when changefield = 'PRICE' then oldvalue end) OldPrice,
    max(case when changefield = 'INTEREST' then oldvalue end) OldInterest
  from h
  where h.aid = a.aid and h.rn = 1
)h

Demo Fiddle

Also note, if you were using a more current version of SQL Server (2016 ) there is inbuilt functionality for this using system versioning with its own extentions to t-sql for this kind of querying.