I have a table like so:
item_id order_date order_qty 1 '1-01-2019' 10 1 '2-05-2019' 20 1 '10-05-2019' 30 2 '5-10-2020' 40 2 '10-02-2018' 10 3 '10-21-2018' 15 4 '10-20-2018' 20
I want to find the total order_qty where the item_id matches, and the order_date is between two date values. The issue is that each item_id has different dates to check so they cannot be a constant in the where clause.
Although this will not run I was thinking something like this:
SELECT * FROM Table Where item_id in (1,2,3) and order_date between (("01-01-2018", "02-02-2018"), ("01-02-2019", "03-22-2020"), ("11-15-2018", "12-04-2019"))
Advertisement
Answer
Do you actually want to iterate, or just return matching results?
Sounds like this might be the direction you want to go:
SELECT item_id, SUM(order_qty) FROM Table WHERE (item_id = 1 AND order_date BETWEEN '01-01-2018' AND '02-02-2018') OR (item_id = 2 AND order_date BETWEEN '01-02-2019' AND '03-22-2020') OR (item_id = 3 AND order_date BETWEEN '11-15-2018' AND '12-04-2019') GROUP BY item_id;
(Although comparing string literals to date fields is not a good idea, use to_date
(Oracle) or equivalent function to translate the string literals to dates. The following is an Oracle example:
SELECT item_id, SUM(order_qty) FROM Table WHERE (item_id = 1 AND order_date BETWEEN TO_DATE('01-01-2018', 'MM-DD-YYYY') AND TO_DATE('02-02-2018', 'MM-DD-YYYY')) OR (item_id = 2 AND order_date BETWEEN TO_DATE('01-02-2019', 'MM-DD-YYYY') AND TO_DATE('03-22-2020', 'MM-DD-YYYY')) OR (item_id = 3 AND order_date BETWEEN TO_DATE('11-15-2018', 'MM-DD-YYYY') AND TO_DATE('12-04-2019', 'MM-DD-YYYY')) GROUP BY item_id;