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

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



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source