CODEWITHSUNDEEP
sql
user2908651
user2908651

Reputation: 33

SQL multiple dates query

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???

Upvotes: 2

Views: 105

Answers (1)

Paul Draper
Paul Draper

Reputation: 83255

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.

Upvotes: 2

Related Questions