'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 |
|---|
