CODEWITHSUNDEEP
sql
KidLu
KidLu

Reputation: 223

Price data from different days into one row/two columns

I have a list of order and a list of historical prices, so let's say

Transactions:

Transaction_no ID_no Price_Traded Trade_Date
T1 a 10.9 20210720
T2 b 58.7 20210720

Prices:

ID_no Price_Theo Date
a 10.5 20210718
a 10 20210719
a 11 20210720
b 62 20210718
b 59 20210719
b 58 20210720

I want to obtain a list where I can compare the traded price with the theoretical price from that day and from the day before. So ideally a list like this:

Transaction_no ID_no Price_Traded Trade_Date Price_Theo_TD Price_Theo TD-1
T1 a 10.9 20210720 11 10
T2 b 58.7 20210720 58 59

Is that possible? Right now I am only able to get a list like:

Transaction_no ID_no Price_Traded Date Price_Theo
T1 a 10.9 20210719 10
T1 a 10.9 20210720 11
T2 b 58.7 20210719 59
T2 b 58.7 20210720 58

The code used here should look like this:

SELECT T.Transaction_no, P.ID_no, T.Price_Traded, P.Date, P.Price_Theo
FROM Transactions T 
    right join Prices P on T.ID_no = P.ID_no
WHERE T.Transaction_no in ('T1', 'T2')
    AND P.Date in (T.Trade_Date, T.Trade_Date - INTERVAL '1' DAY)

Note: I do know the discussion from SQL Server: combining multiple rows into one row and other boards. But my questions differs, since I want information from the second row into an additional column. I do not want to concatenate stuff in one variable.

Note2: I thought about PIVOT - but this only works with aggregating functions and I do not want to aggregate anything.

Thanks a lot! Would be happy for any help. Also if there is an existing discussion on that topic.

Cheers

KidLu

Upvotes: 2

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270493

Use two joins:

SELECT T.Transaction_no, P.ID_no, T.Price_Traded,
       P.Date, P.Price_Theo,
       P1.Date, P1.Price_Theo
FROM Transactions T LEFT JOIN
     Prices P 
     ON P.ID_no = T.ID_no AND
        P.Date = T.Trade_Date LEFT JOIN
     Prices P1
     ON P1.ID_no = T.ID_no AND
        P1.Date = T.Trade_Date - INTERVAL '1' DAY;

Upvotes: 1

Related Questions