----- 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
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.1/307 - Release Date: 10/04/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
