I'm putting your question back on the mailing list where it belongs. That
enables others to help and to learn from the discussion, either now or in
the future via the archives.
--
Rhino
----- Original Message -----
From: "H L" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, April 12, 2006 2:00 PM
Subject: Re: Select a value between dates.
>From: "Rhino" <[EMAIL PROTECTED]>
To: "H L" <[EMAIL PROTECTED]>,<mysql@lists.mysql.com>
Subject: Re: Select a value between dates. Date: Tue, 11 Apr 2006
16:35:15 -0400
----- Original Message ----- From: "H L" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Tuesday, April 11, 2006 4:05 PM
Subject: Select a value between dates.
I am far from an mysql expert... but is there a way to select between
dates in a table and check if a value exists in all fields between dates.
If one day between those dates cannot be booked i dont want to get it in
my SUM resultset.
I want to calculate Sum between those dates and i one idea i have is to
use the count to determine if i can use the result but it does not feels
right. ..
anyway the query looks like this
SELECT
`companyobjects`.`objectid`,
`companyobjects`.`objectname`,
Sum(`completereservationcredentials`.`price`),
Count(`completereservationcredentials`.`price`)
FROM
`completereservationcredentials`
Inner Join `companyobjects` ON
`completereservationcredentials`.`objectid` = `companyobjects`.`objectid`
WHERE
`completereservationcredentials`.`avaliable` = 1 AND
`completereservationcredentials`.`reservationid` = 0 AND
`completereservationcredentials`.`thedate` >= '2006-08-01' AND
`completereservationcredentials`.`thedate` < '2006-08-10' AND
`companyobjects`.`companyid` = 'somecompanyid'
GROUP BY
`completereservationcredentials`.`objectid`
ORDER BY
`companyobjects`.`objectname` ASC
Your question and your SQL seem to be contradicting one another.
You seem to be asking whether it is possible to determine if a given date
from a table lies between two arbitrary dates. Of course it can, as you
demonstrate in your SQL:
`completereservationcredentials`.`thedate` >= '2006-08-01' AND
`completereservationcredentials`.`thedate` < '2006-08-10' AND
By the way, you might find it more concise to say:
`completereservationcredentials`.`thedate` between '2006-08-01' AND
'2006-08-09'
(I subtracted one day from the original end date of the range to make sure
that the August 10 was not chosen; your original query wanted the date
only if was LESS THAN August 10. The between keyword always gets dates
that in a given range and INCLUDES both ends of the range.)
You also talk about using sum() on days or dates but your query is
actually summing prices, which is quite a different matter.
I think you need to clarify just what you want to know and ask a clearer
question.
--
Rhino
Hi thanks!
Well in this case i want to query on the keys objectid and date to check
if object can be booked or not.
If one of days between is booked i do not want to calculate price for any
day which it does now. I want it to discard that object as it cannot be
booked that period. I do not want a false sum result of price for 13 days
instead of 14 days..
So, basically, you only want to return data if there are rows for each and
every date in the date range?
If that is what you mean, I think you'll need to modify your WHERE to return
rows only when every single day in the range is available for booking; if
one or more days in the range are not available for booking, don't return
any rows at all. An EXISTS might be handy for that problem.
Something like this might do the job, although I've never tried a query with
multiple exists in it before and don't know if it will work the way I'm
picturing:
<your existing SELECT and FROM>
WHERE
EXISTS (select * from completereservationcredentials where
completereservationcredentials.thedate = '2006-08-01') AND
EXISTS (select * from completereservationcredentials where
completereservationcredentials.thedate = '2006-08-02') AND
...
EXISTS (select * from completereservationcredentials where
completereservationcredentials.thedate = '2006-08-10')
In other words, write a separate EXISTS subquery for each individual date
within the range and be sure to connect each of the EXISTS with AND. The
EXISTS query doesn't actually return any data: in an EXISTS query, the
'SELECT *' really means "return a true/false flag".
I can't try this myself since I don't have a recent enough copy of MySQL -
no subquery support in my version! - but it's worth a try.
It's pretty ugly though, especially if you have a date range of more than a
very few days, since you'd have to write one subquery for each date in the
range. There may well be a much easier way to do it but that's all I can
think of offhand. Maybe someone else on the list can suggest a better
approach.
--
Rhino
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.1/309 - Release Date: 11/04/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
