Select and group multiple range of dates in oracle SQL

Question

I want to Select the same range of dates and group them by year. In this case I want the total of the bills of a customer per year from September and October.

My Code right now:

SELECT c.name, c.telefonnumber, TO_CHAR(b.billdate, 'YYYY'), sum(b.sume)
FROM customer c, bill b
WHERE b.customerId = c.id
AND b.datum BETWEEN '01.10.2016' AND '30.09.2016'
GROUP BY c.name, c.telefonnumber, TO_CHAR(b.billdate, 'YYYY');

This Query works perfectly but when I want to add another year the result is an endless loop:

AND b.datum BETWEEN '01.10.2016' AND '30.09.2016'
OR b.datum BETWEEN '01.10.2015' AND '30.09.2015'

I also tried to do it in a String way. In this case when I only look for the results of September it works perfectly, but again as soon as I add the OR it becomes again an endless loop:

AND TO_CHAR(kp.tmstmp) LIKE '%.09.%' 
OR TO_CHAR(kp.tmstmp) LIKE '%.10.%'

I know I am somehow missing something in the OR operator, but since this is only happening me with dates I am a bit confused.

When mixing AND and OR you should really use brackets around the things you are ORing. A common mistake to make and it's what is causing your query to hang. — Matthew Hart
– Matthew Hart, Commented Nov 23, 2017 at 11:26

Sanders the Softwarer · Accepted Answer · 2017-11-23 11:26:08Z

2

This query works fine and return 0 rows, I guess - exactly the number of dates between 01.10.2016 and 30.09.2016 :)

If you wish to check several ranges, you should enclose them into braces:

... and
  ( 
    b.datum between date '2016-10-01' and date '2017-09-30' or
    b.datum between date '2015-10-01' and date '2016-09-30' or
    ... 
  ) and
...

answered Nov 23, 2017 at 11:26

Sanders the Softwarer

2,5061 gold badge15 silver badges29 bronze badges

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

Comments

Tim Biegeleisen · Accepted Answer · 2017-11-23 11:26:01Z

0

It looks like your date criteria in the WHERE clause are being phrased incorrectly. Try this instead:

SELECT c.name, c.telefonnumber, TO_CHAR(b.billdate, 'YYYY'), sum(b.sume)
FROM customer c, bill b
WHERE b.customerId = c.id AND
      (b.datum BETWEEN TO_DATE('01.10.2016', 'DD.MM.YYYY') AND
                      TO_DATE('30.09.2016', 'DD.MM.YYYY') OR
       b.datum BETWEEN TO_DATE('01.10.2015', 'DD.MM.YYYY') AND
                      TO_DATE('30.09.2015', 'DD.MM.YYYY'));

I made two changes, namely making certain that the date checks occur in one logical place, surrounded by outer parentheses, and that we use bona fide dates when comparing against the datum column.

answered Nov 23, 2017 at 11:26

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Collectives™ on Stack Overflow

Select and group multiple range of dates in oracle SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related