Subtracting two rows from the same table based on date and hour

Question

I have two tables I would like to join.

Table A

  Date     | Hour | Direction | Qty 
2018-11-20    1      DE/UK       2 
2018-11-20    2      DE/UK       6

Table B

   Date    | Hour | Area  | Price
2018-11-20    1      DE       5 
2018-11-20    2      DE       4 
2018-11-20    1      UK       3 
2018-11-20    2      UK       9

I want to join them like this:

Table C

 Date     | Hour | Direction | Qty | AreaFrom | AreaTo | PriceFrom | PriceTo | Profit
2018-11-20    1      DE/UK       2      DE       UK         5           3      3-5 = -2
2018-11-20    2      DE/UK       6      DE       UK         4           9        5

I have tried by CROSS Join and other kinds of joins, but have not been able to make it work.

Other related questions I have looked at so far:

SQL subtract two rows based on date and another column

Selecting two rows from the same table

You'll be looking to `pivot` your area and price columns accordingly from table b. Then join on date/hour back to table A. — Adam, Nov 26 '18 at 13:42

score 1 · Answer 1 · answered Nov 26 '18 at 14:16

It might not be as complex as Adam makes out. If the TableA had a "directionFrom" and a "directionTo" columns you'd probably have worked this out by yourself. So let's cut up the direction field into two columns:

SELECT 
  a.[Date], a.[Hour], a.Direction, a.Qty, 
  f.Area as AreaFrom, t.Area as AreaTo, 
  f.Price as priceFrom, t.Price as PriceTo, 
  t.Price-f.Price as profit
FROM 
  TableA a
  INNER JOIN TableB f 
  ON 
    a.[Date] = f.[Date] and 
    a.Hour = b.[Hour] and
    LEFT(a.Direction, 2) = f.Area --take the left two as the area from
  INNER JOIN TableB t 
  ON 
    a.[Date] = t.[Date] and 
    a.Hour = t.[Hour] and
    RIGHT(a.Direction, 2) = t.Area --take the right two as the area to

If you ever have areas with more than two letter codes, you'll have to SUBSTRING on the CHARINDEX of the / instead

Subtracting two rows from the same table based on date and hour

1 Answers1