SQL Query – how to translate this excel formula into a query

I have an existing excel formula. The logic is this:

1. Start looking when dates on Table 2 “Date/Time” are greater than Table 1 “Open_Date”
2. Stop looking when dates on Table 2 “Date/Time” that are less than Table 1 “Close_Date”
3. If Table 1 “Action” says “Buy”, then find where the Table 2 “Low” is less than Table 1 Column L “FixedSL”.
4. If Table 1 “Action” says “Sell”, then find where the Table 2 “High” is less than Table 1 Column L “FixedSL”.
5. If match is found, return the “Date/Time” in Table 2 and overwrite the value in Table 1 “Time_Hit_Fixed_SL” for that row.

Here are samples of the data tables: Table 1

Open_Date,       Close_Date,   Action, FixedSL,  Time_Hit_Fixed_SL
6/1/2020 3:56,  6/1/2020 4:24,  Buy,   1.8502,   6/1/2020 5:01
6/1/2020 4:44,  6/1/2020 8:19,  Sell,  1.8411,   6/1/2020 10:12
6/1/2020 8:22,  6/1/2020 8:54,  Sell,  1.8335,   6/1/2020 10:12

Table 2

Date/Time,        Open,     High,     Low,    Close
06/01/2020 03:57,  1.8503,  1.8503,  1.8501,  1.8501
06/01/2020 03:58,  1.8501,  1.8503,  1.8501,  1.8502
06/01/2020 03:59,  1.8501,  1.8504,  1.8501,  1.8504
06/01/2020 04:00,  1.8501,  1.8505,  1.8501,  1.8503
06/01/2020 04:01,  1.8504,  1.8504,  1.8504,  1.8504

The forumula’s output for the Table 1’s first row would be 06/01/2020 03:57 overwriting the existing value in the “Time_Hit_Fixed_SL” column.

What I am looking to accomplish is to perform this same action in SQL with the data in 2 Tables.

I am new to SQL and searched, but couldn’t find the right direction to obtain the guidance of where to start with writing queries to replace advanced excel formula logic. I appreciate the help and guidance!

Answer

You can use window function MIN() to get the value of the column Date/Time.

If you want a SELECT statement that returns your expected result:

SELECT DISTINCT t1.Open_Date, t1.Close_Date, t1.Action,  
  COALESCE(
    MIN(
      CASE t1.Action
        WHEN 'Buy' THEN CASE WHEN t2.Low < t1.FixedSL THEN t2.Date_Time END
        WHEN 'Sell' THEN CASE WHEN t2.High < t1.FixedSL THEN t2.Date_Time END
      END
    )
    OVER (PARTITION BY t1.Open_Date, t1.Close_Date),
    t1.Time_Hit_Fixed_SL
  ) Time_Hit_Fixed_SL
FROM Table1 t1 LEFT JOIN Table2 t2
ON t2.Date_Time > t1.Open_Date AND t2.Date_Time < t1.Close_Date

If you want to update Table1:

UPDATE Table1 AS t1
SET Time_Hit_Fixed_SL = COALESCE(
  (
    SELECT 
      MIN(
        CASE t1.Action
          WHEN 'Buy' THEN CASE WHEN t2.Low < t1.FixedSL THEN t2.Date_Time END
          WHEN 'Sell' THEN CASE WHEN t2.High < t1.FixedSL THEN t2.Date_Time END
        END
      )
    FROM Table2 t2  
    WHERE t2.Date_Time > t1.Open_Date AND t2.Date_Time < t1.Close_Date
  ),
  t1.Time_Hit_Fixed_SL
)

See the demo.