Query to show old and new values from another table

Question

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    10  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             15          10         12/15/21 
11      update_flag                 M          1/1/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. Example:

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

How can I do that ? Thank you.

Answer 1

You can try to use OUTER JOIN with the condition aggregate function.

Query 1:

SELECT A.AID,
       MAX(ISNULL(CASE WHEN h.ChangeField = 'PRICE' THEN h.OldValue END,A.PRICE)) OldPRICE,
       MAX(ISNULL(CASE WHEN h.ChangeField = 'PRICE' THEN h.NewValue END,A.PRICE)) NewPRICE,
       MAX(ISNULL(CASE WHEN h.ChangeField = 'LOT' THEN h.OldValue END,A.LOT)) OldLot,
       MAX(ISNULL(CASE WHEN h.ChangeField = 'LOT' THEN h.NewValue END,A.LOT)) NewLot,
       MAX(ISNULL(CASE WHEN h.ChangeField = 'INTEREST' THEN h.OldValue END,A.INTEREST)) OldInterest,
       MAX(ISNULL(CASE WHEN h.ChangeField = 'INTEREST' THEN h.NewValue END,A.INTEREST)) NewInterest
FROM A 
LEFT JOIN B ON A.AID = B.AID
LEFT JOIN History h ON B.BID = h.BID
GROUP BY A.AID

Results:

| AID | OldPRICE | NewPRICE | OldLot | NewLot | OldInterest | NewInterest |
|-----|----------|----------|--------|--------|-------------|-------------|
|   1 |     1700 |     1500 |     15 |     10 |         0.5 |         0.5 |
|   2 |     2500 |     2500 |     20 |     20 |         1.5 |         1.5 |