CODEWITHSUNDEEP
sqloracle-database
Jorge Nitales
Jorge Nitales

Reputation: 159

Select and group multiple range of dates in oracle SQL

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.

Upvotes: 0

Views: 2356

Answers (2)

Sanders the Softwarer
Sanders the Softwarer

Reputation: 2496

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

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

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.

Upvotes: 0

Related Questions