Select a value between dates.

[H L]

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

_
Lättare att hitta drömresan med MSN Resor http://www.msn.se/resor/


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Select Sum with union, tricky question perhaps not for you

[H L]

The solution is to redesign your tables. You need to split into separate
columns the values you want to maintain. You do not want to keep the flat
file design you are currently trying to use.

CREATE TABLE calendar (
  objectid,
  year,
  dayofyear,
  ... other fields...
)

CREATE TABLE price (
  objectid,
  year,
  dayofyear,
  price
)

Having a separate column for each day of the year may make sense to a
person but as you have discovered, it is extremely difficult to use for
any kind of ad-hoc querying.  A more normalized data structure will be
almost as efficient in space usage but 1000s of times more efficient for
querying.  There is no simple way to write a query that spans years with
the table structures you currently have.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



Hi thanks for the quick response!

Well i think you are right about this. I will probably have to redesign the 
database. What do you think of using DATE instead of year,dayofyear or is 
there problems i could get into then.  It would be great to be able to use 
select from price where date=somedate AND date = tosomedate


/Henrik

_
Hitta rätt på nätet med MSN Search http://search.msn.se/


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Select Sum with union, tricky question perhaps not for you

[H L]

Hi, i have a problem to  select sum from same table using UNION.  The key 
question is there a way of combining two questions in one so the resulting 
objectid in query1 is only used in query 2 in a smart way. Eg. if only one 
object is avaliable in a search first year then only check that objectid for 
next year and append the sum in the question.


I have 4 tables
companyobjects that contains the key (companyid,objectid,name etc)

and  i have
calendar the table looks similar to this 
(objectid,year,day1,day2.day365)

price that looks similar to this (objectid,year,day1,day2.day365)
reservation that looks similar to this (objectid,year,day1,day2.day365)


I have tried as follows below, as you can see i want to calculate price 
discount and amount to pay from same table but from 2 years. I have tried 
removed all my where clause below and the result is not correct it is not 
from both querys.



Maybe you know an easier way, totaly diffrent way that i have not thought of 
perhaps.


Thanks in advance /Henrik


SELECT
`companyobjects`.`objectid`,
Sum(`objectprice`.`d362` +`objectprice`.`d363` 
+`objectprice`.`d364`+`objectprice`.`d365`),
Sum((`objectprice`.`d362` +`objectprice`.`d363` 
+`objectprice`.`d364`+`objectprice`.`d365`)*0.1),
Sum((`objectprice`.`d362` +`objectprice`.`d363` 
+`objectprice`.`d364`+`objectprice`.`d365`)*0.9)

FROM
`companyobjects`
Inner Join `objectprice` ON `companyobjects`.`objectid` = 
`objectprice`.`objectid`
Inner Join `objectreservation` ON `companyobjects`.`objectid` = 
`objectreservation`.`objectid`
Inner Join `objectcalendar` ON `companyobjects`.`objectid` = 
`objectcalendar`.`objectid`


WHERE
`companyobjects`.`companyid` = 'C050319112022656' AND
`companyobjects`.`maxnrofguests` = '1' AND
`objectprice`.`year` = '2006' AND
`objectreservation`.`year` = '2006' AND
`objectcalendar`.`year` = '2006'

GROUP BY
`companyobjects`.`objectid`

UNION ALL

SELECT
`companyobjects`.`objectid`,
Sum(`objectprice`.`d1` +`objectprice`.`d2`),
Sum((`objectprice`.`d1` +`objectprice`.`d2`)*0.1),
Sum((`objectprice`.`d1` +`objectprice`.`d2`)*0.9)
FROM
`companyobjects`
Inner Join `objectprice` ON `companyobjects`.`objectid` = 
`objectprice`.`objectid`
Inner Join `objectreservation` ON `companyobjects`.`objectid` = 
`objectreservation`.`objectid`
Inner Join `objectcalendar` ON `companyobjects`.`objectid` = 
`objectcalendar`.`objectid`

WHERE
`companyobjects`.`companyid` = 'C050319112022656' AND
`companyobjects`.`maxnrofguests` = '1' AND
`objectprice`.`year` = '2007' AND
`objectreservation`.`year` = '2007' AND
`objectcalendar`.`year` = '2007'
GROUP BY
`companyobjects`.`objectid`






I

_
Hitta rätt på nätet med MSN Search http://search.msn.se/


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]