I have two tables [sales] and [payments] and I want to see if a payment is made between the sale’s product start date and the end date of the sale BUT sometimes the product hasn’t started yet!
Sales Table
| Customer | Sale_Date | Product_Start | Product_End |
|---|---|---|---|
| ABC— | 20240801 | 20240801—– | 20240831— |
| DEF— | 20240801 | 000000 —– | 000000 — |
Payment Table
| Customer | Payment_Date |
| ——– | ———— |
| ABC— | 20240815—- |
- SELECT S.*, nvl(P.Payment_Date,000000)
- FROM sales s
- LEFT OUTER JOIN Payments p
- ON p.Payment_Date BETWEEN
- CASE WHEN Product_Start > 000000
- THEN Product_Start AND Product_End
- WHEN Product_Start = 000000
- THEN 29991201 AND 29991231
- END
— I.E. Sometime in the future so it is impossible to pick up a payment as the product hasn’t started
Expected Results:
| Customer | Sale_Date | Product_Start | Product_End | Payment_Date |
|---|---|---|---|---|
| ABC— | 20240801 | 20240801—– | 20240831— | 20240815—- |
| DEF— | 20240801 | 000000 —– | 000000 —- | 000000 —– |
above gives me an error saying missing keyword and highlights the AND on row 6
I tried adding in something after the END eg END = 1 but same error