I have two records that look like this in TABLE1:
| KEY | RECORD_START_TS | RECORD_END_TS |
|---|---|---|
| 1 | 2023-08-06 00:00:00 | 9999-12-31 00:00:00 |
| 2 | 2022-11-27 00:00:00 | 2023-08-05 23:59:59 |
I need to execute a conditional LEFT JOIN where if my date minus 1 day is equal to the ending timestamp of a record, then change the conditions of the join. I have tried the below code which throws a syntax error:
LEFT JOIN ON CASE
WHEN DATE'2023-08-06' - 1 = CAST(RECORD_END_TS AS DATE) THEN DATE'2023-08-06' BETWEEN CAST(RECORD_START_TS AS DATE) AND CAST(RECORD_END_TS AS DATE)
ELSE DATE'2023-08-06' - 1 BETWEEN CAST(RECORD_START_TS AS DATE) AND CAST(RECORD_END_TS AS DATE) END
I have also tried this, which grabs the record with KEY = 2, but the record I am expecting to grab is KEY = 1:
LEFT JOIN ON CASE
WHEN DATE'2023-08-06' - 1 = CAST(RECORD_END_TS AS DATE) THEN DATE'2023-08-06'
ELSE DATE'2023-08-06' - 1 END BETWEEN CAST(RECORD_START_TS AS DATE) AND CAST(RECORD_END_TS AS DATE)
EDIT: My problem is a very long one, but I will do my best to explain. I have 2 tables - an order data table, and a type 2 customer activity table (this is the table I provided a fake example for).
The activity table gets new records only when the order table has activity on an order that meets criteria to make the update to the activity table, and the updates come 1 day after the order complete date.
In the data I provided, an order completed on 2023-08-05, this order generated a new record. So, KEY = 2 gets an updated RECORD_END as 2023-08-05-23:59:59, AND KEY = 1 is a brand new insert with a start time 1 day after the order completed - 2023-08-06.
In my situation, another order takes place a day later on 2023-08-06 - however, not an order with sufficient activity to create a new record in the activity table.
Now, in all these cases I need to see what activity record was active before the order, and after the order. Since the order that took place on 2023-08-06 did not generate a new activity record then the latest activity record is the match for both before and after the order is the newest record.
To check activity before the order we take the completion date (2023-08-06) - 1 day. But we need to see that if this order completed 1 day after the last record change AND there was no change created from this latest record itself, then we need the latest record.
Sorry, I know that is very confusing probably due to poor explanation, I fully understand removing the question if needed.
Suppose first table is a, and temporal dimension is b
Then the join will be like this:
FROM a
LEFT JOIN b
ON (a.your_date -1) >= cast(b.RECORD_START_TS as date)
AND (a.your_date -1) < cast(b.RECORD_END_TS as date)
OR (including the RECORD_END_TS )
FROM a
LEFT JOIN b
ON (a.your_date -1) >= cast(b.RECORD_START_TS as date)
AND (a.your_date -1) <= cast(b.RECORD_END_TS as date)