I have a table with 10+ million records and the structure is as follows -
| Store ID | Item ID | item_active_Date | item_inactive_date |
|---|---|---|---|
| NY0001 | FMC0001 | 2021-10-30 | 2021-11-30 |
| NY0001 | FMC0002 | 2021-01-10 | 2021-06-14 |
| NY0002 | FMC0003 | 2021-09-01 | 2021-09-10 |
| NY0002 | FMC0004 | 2021-01-01 | 2021-03-31 |
| NY0003 | FMC0005 | 2021-04-01 | 2021-05-30 |
| NY0003 | FMC0006 | 2021-06-02 | 2021-06-24 |
| NY0004 | FMC0007 | 2021-01-02 | Null |
The need is to identify the item_ID's available between a given date range. Only item_active and item_inactive dates are available.
Consider the item_ID's that were available between "2021-06-15" and "2021-11-25". The expected result is as follows (When item_inactive date is not available consider it as today)-
| Store ID | Item ID | item_active_Date | item_inactive_date |
|---|---|---|---|
| NY0001 | FMC0001 | 2021-10-30 | 2021-11-30 |
| NY0002 | FMC0003 | 2021-09-01 | 2021-09-10 |
| NY0003 | FMC0006 | 2021-06-02 | 2021-06-24 |
| NY0004 | FMC0007 | 2021-01-02 | Null |
Something along these lines should work, I believe:
SELECT item_ID FROM my_table
WHERE item_active_date <= $endDate
AND (item_inactive_date IS NULL
OR item_inactive_date > $startDate);
Although you'll want to make sure your SQL engine is interpreting the the values in these inequalities as dates, not strings, since the format your table is using has days between months and years.