[thelist] SQL query question about AND OR and date ranges A SOLUTION
Jay Blanchard
jay.blanchard at niicommunications.com
Thu Sep 5 09:47:00 CDT 2002
[snip]
It returns dates from outside of the range specified. I do not understand
why it does this and cannot find anything on the web or in a book that
explains the behavior close enough for me to discern. Since I cannot find an
explanation I cannot find a proper solution either. Has anyone seen this
behavior or would anyone have a solution?
[/snip]
Just for giggles I tried this;
mysql> SELECT cdr.discn_dt, COUNT(cdr.ani) AS quantity, SUM(cdr.calldur/60)
AS minutes
-> FROM tblCDR cdr LEFT OUTER JOIN tblCDRExempt e
-> ON cdr.ani = e.ExemptCDR
-> WHERE e.ExemptCDR IS NULL
-> AND cdr.dialedno LIKE '800%'
-> AND cdr.discn_dt BETWEEN '2002-07-01' AND '2002-07-30'
-> OR cdr.dialedno LIKE '866%'
-> AND cdr.discn_dt BETWEEN '2002-07-01' AND '2002-07-30'
-> OR cdr.dialedno LIKE '877%'
-> AND cdr.discn_dt BETWEEN '2002-07-01' AND '2002-07-30'
-> OR cdr.dialedno LIKE '888%'
-> AND cdr.discn_dt BETWEEN '2002-07-01' AND '2002-07-30'
-> GROUP BY cdr.discn_dt;
which essentially performs the date range on each OR. Then John said
*slapping self in forhead very hard* "Put the brackets in. If you think
about it for a moment you'll figure out why for yourself :-)"
mysql> SELECT cdr.discn_dt, COUNT(cdr.ani) AS quantity, SUM(cdr.calldur/60)
AS minutes
-> FROM tblCDR cdr LEFT OUTER JOIN tblCDRExempt e
-> ON cdr.ani = e.ExemptCDR
-> WHERE e.ExemptCDR IS NULL
-> AND cdr.discn_dt BETWEEN '2002-07-01' AND '2002-07-30'
-> AND (cdr.dialedno LIKE '800%'
-> OR cdr.dialedno LIKE '866%'
-> OR cdr.dialedno LIKE '877%'
-> OR cdr.dialedno LIKE '888%')
-> GROUP BY cdr.discn_dt;
I thought when I got up this morning I should have gone back to bed. Now
that theory (the only one of this very short day thus far) has been proven
correct whilst I struggle with the most basic of things. Thanks John.
Thanks!
Jay
*****************************************************
* Texas PHP Developers Conf Spring 2003 *
* T Bar M Resort & Conference Center *
* New Braunfels, Texas *
* Contact jay.blanchard at niicommunications.com *
* *
* Want to present a paper or workshop? Contact now! *
*****************************************************
More information about the thelist
mailing list