Skip to content
Advertisement

SQL- Iterate through a list of WHERE clause values

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;
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement