SQL Server query to show old and new values from another table-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    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.

CodePudding user response：

First you need to join all three tables and aggregate per AID. Once you have that, the query needs to selectively pick the values from the history should they exist.

For example:

select
  a.aid,
  max(case when h.changefield = 'PRICE' then coalesce(h.oldvalue, a.price) end) as oldprice,
  max(case when h.changefield = 'PRICE' then coalesce(h.newvalue, a.price) end) as newprice,
  max(case when h.changefield = 'LOT' then coalesce(h.oldvalue, a.lot) end) as oldlot,
  max(case when h.changefield = 'LOT' then coalesce(h.newvalue, a.lot) end) as newlot,
  max(case when h.changefield = 'INTEREST' then coalesce(h.oldvalue, a.interest) end) as oldinterest,
  max(case when h.changefield = 'INTEREST' then coalesce(h.newvalue, a.interest) end) as newinterest 
from table_a a
left join table_b b on b.aid = a.aid
left join history h on h.bid = b.bid
group by a.aid

CodePudding user response：

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 |