How to select only ids that contain a column value of 0 in a date range?

Question

I have a table t like:

VALID_FROM | VALID_TO   | id | stock
2020-10-01 | 2020-10-02 | 1  | 10
2020-10-02 | 2020-10-04 | 1  | 9
2020-10-04 | 2020-10-08 | 1  | 5
2020-11-12 | 2020-11-26 | 1  | 4
...        | ...        | 1  |
2020-12-15 | 2020-12-16 | 1  | 0

2020-10-01 | 2020-10-02 | 2  | 10
2020-10-02 | 2020-10-04 | 2  | 9
2020-10-04 | 2020-10-08 | 2  | 5
...        | ...        | 2  |
2020-12-15 | 2020-12-16 | 2  | 1

2020-11-12 | 2020-11-26 | 3  | 13
...        | ...        | 3  |
2020-11-30 | 2020-11-30 | 3  | 0

I filter for a specific date range via:

SELECT *
FROM table AS t
WHERE t.VALID_FROM >= ADD_DAYS('2020-11-26', -14)
AND t.VALID_TO <= ADD_DAYS('2020-11-26', +14)

But now I need to find every "id" that reaches a "stock" of 0 in that date range.

From that result I need the entries:

1. id
2. stock at the date of ADD_DAYS('2020-11-26', -14) - here '2020-11-12'
3. the day at which the stock reached 0

So the end table should look like this:

id | stock_at_date-14days | day_of_stock_reaching_0
1  | 4                    | 2020-12-16
3  | 13                   | 2020-11-30

How can I achieve this with SQL?

Answer 1

Try self join.

SELECT t.*, t0.*
FROM table AS t
JOIN table AS t0 ON t.id = t0.Id
     AND t.VALID_FROM = ADD_DAYS('2020-11-26', -14)
     AND t0.Stock = 0 
     AND t0.VALID_FROM <= ADD_DAYS('2020-11-26', -14)
     AND t0.VALID_TO <= ADD_DAYS('2020-11-26', +14)

Answer 2

You can use join:

SELECT t.*, t2.stock as days_before_14
FROM table t LEFT JOIN
     table t2
     ON t.id = t2.id AND
        ADD_DAYS(t.VALID_FROM, -14) >= t2.VALID_FROM AND
        ADD_DAYS(t.VALID_FROM, -14) < t2.VALID_TO AND        
WHERE t.VALID_FROM >= ADD_DAYS('2020-11-26', -14) AND
      t.VALID_TO <= ADD_DAYS('2020-11-26', +14) AND
      t.stock = 0;