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 |
CodePudding user response：
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 since format your table is using has days between months and years.
CodePudding user response：
You can do this :
SELECT * FROM Sample
WHERE
item_active_Date BETWEEN '2021-06-15' and '2021-11-25'
OR item_inactive_date BETWEEN '2021-06-15' and '2021-11-25'
OR item_active_Date IS NULL