SQL multiple dates query

Question

I'm working on a query where I have to find 3 or more orders on a part that will have multiple date ranges. The problem I'm having is that it looks like the query isn't looking at the date ranges specifically...it's looking at a complete range of dates.

 SELECT partnumber, count(orders.partorderid) as numberoforders 
 FROM orders
 WHERE(orderdate between '2013-10-07' and '2013-10-13') or (orderdate between '2013-10-14' and '2013-10-20')
 GROUP BY partnumber
 HAVING COUNT(partorderid) > 2

The query returns the numberoforders between 10/07-10/20, not 10/07-10/13 and 10/14-10/20???

Paul Draper · Accepted Answer · 2013-10-30 20:15:39Z

2

Try this:

SELECT partnumber,
    SUM(CASE WHEN orderdate BETWEEN '2013-10-07' and '2013-10-13' THEN 1 END) week1,
    SUM(CASE WHEN orderdate BETWEEN '2013-10-14' and '2013-10-20' THEN 1 END) week2
FROM orders
GROUP BY partnumber
HAVING SUM(CASE WHEN orderdate BETWEEN '2013-10-07' and '2013-10-13' THEN 1 END) > 2
    AND SUM(CASE WHEN orderdate BETWEEN '2013-10-14' and '2013-10-20' THEN 1 END) > 2

Or you might prefer:

SELECT *
FROM (
    SELECT partnumber,
        SUM(CASE WHEN orderdate BETWEEN '2013-10-07' and '2013-10-13' THEN 1 END) week1,
        SUM(CASE WHEN orderdate BETWEEN '2013-10-14' and '2013-10-20' THEN 1 END) week2
    FROM orders
    GROUP BY partnumber
) t WHERE week1 > 2 AND week2 > 2

which has a less repetition.

FYI, if orderdate is a date, you are fine. But if it has and date a and a non-zero time, you will be missing orders that are after 2013-10-13 00:00:00 and before 2013-10-14 00:00:00.

edited Oct 30, 2013 at 20:15

answered Oct 29, 2013 at 20:34

Paul Draper

84.2k53 gold badges216 silver badges303 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user2908651 Over a year ago

Thanks for the quick reply! The only problem I'm having with this is that it shows blanks or < 3 in week1 or week2 if one of the conditions are met. Can these values be eliminated from the results?

Paul Draper Over a year ago

It depends what you mean by eliminated. Do you want to have the value in the offending column as NULL, or remove the whole record for the part number?

user2908651 Over a year ago

Remove the record for the part number from the query results.

Paul Draper Over a year ago

Easy...simply change WHERE week1 > 2 OR week2 > 2 TO WHERE week1 > 2 AND week2 > 2

user2908651 Over a year ago

I was never able to get the second example to run due to an incorrect syntax error near WHERE. I changed the OR to AND in the HAVING clause from the first example and the output is great, but now it's missing values. Some of the orders that were > 3 aren't pulling.

|

Collectives™ on Stack Overflow

SQL multiple dates query

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related