sql charateristic function for avg dates
- by holden
I have a query which I use to grab specific dates and a price for the date, but now I'd like to use something similar to grab the avg prices for particular days of the week.
Here's my current query which works for specific dates to pull from a table called availables:
SELECT rooms.name, rooms.roomtype, rooms.id, max(availables.updated_at),
MAX(IF(to_days(availables.bookdate) - to_days('2009-12-10') = 0, (availables.price*0.66795805223432), '')) AS day1,
MAX(IF(to_days(availables.bookdate) - to_days('2009-12-10') = 1, (availables.price*0.66795805223432), '')) AS day2,
MAX(IF(to_days(availables.bookdate) - to_days('2009-12-10') = 2, (availables.price*0.66795805223432), '')) AS day3,
MAX(IF(to_days(availables.bookdate) - to_days('2009-12-10') = 3, (availables.price*0.66795805223432), '')) AS day4,
MAX(IF(to_days(availables.bookdate) - to_days('2009-12-10') = 4, (availables.price*0.66795805223432), '')) AS day5,
MAX(IF(to_days(availables.bookdate) - to_days('2009-12-10') = 5, (availables.price*0.66795805223432), '')) AS day6,
MAX(IF(to_days(availables.bookdate) - to_days('2009-12-10') = 6, (availables.price*0.66795805223432), '')) AS day7,
MIN(spots) as spots
FROM `availables`
INNER JOIN rooms
ON availables.room_id=rooms.id
WHERE rooms.hotel_id = '5064' AND bookdate
BETWEEN '2009-12-10' AND DATE_ADD('2009-12-10', INTERVAL 6 DAY)
GROUP BY rooms.name
ORDER BY rooms.ppl
My first stab which doesn't work, probably because the DAYSOFWEEK function is much different from the to_days...
SELECT rooms.id, rooms.name,
MAX(IF(DAYOFWEEK(availables.bookdate) - DAYOFWEEK('2009-12-10') = 0, (availables.price*0.66795805223432), '')) AS day1,
MAX(IF(DAYOFWEEK(availables.bookdate) - DAYOFWEEK('2009-12-10') = 1, (availables.price*0.66795805223432), '')) AS day2,
MAX(IF(DAYOFWEEK(availables.bookdate) - DAYOFWEEK('2009-12-10') = 2, (availables.price*0.66795805223432), '')) AS day3,
MAX(IF(DAYOFWEEK(availables.bookdate) - DAYOFWEEK('2009-12-10') = 3, (availables.price*0.66795805223432), '')) AS day4,
MAX(IF(DAYOFWEEK(availables.bookdate) - DAYOFWEEK('2009-12-10') = 4, (availables.price*0.66795805223432), '')) AS day5,
MAX(IF(DAYOFWEEK(availables.bookdate) - DAYOFWEEK('2009-12-10') = 5, (availables.price*0.66795805223432), '')) AS day6,
MAX(IF(DAYOFWEEK(availables.bookdate) - DAYOFWEEK('2009-12-10') = 6, (availables.price*0.66795805223432), '')) AS day7,rooms.ppl AS spots FROM `availables`
INNER JOIN `rooms` ON `rooms`.id = `availables`.room_id
WHERE (rooms.hotel_id = 5064 AND rooms.ppl > 3 AND availables.price > 0 AND availables.spots > 1)
GROUP BY rooms.name
ORDER BY rooms.ppl
Maybe i'm making this crazy hard and someone knows a much simpler way.
It takes data that looks like this
#Availables
id room_id price spots bookdate
1 26 $5 5 2009-10-20
2 26 $6 5 2009-10-21
to:
+----+-------+--------------------+---------------------+---------------------+---------------------+------+------+------+------+
| id | spots | name | day1 | day2 | day3 | day4 | day5 | day6 | day7 |
+----+-------+--------------------+---------------------+---------------------+---------------------+------+------+------+------+
| 25 | 4 | Blue Room | 14.9889786921381408 | 14.9889786921381408 | 14.9889786921381408 | | | | |
| 26 | 6 | Whatever | 13.7398971344599624 | 13.7398971344599624 | 13.7398971344599624 | | | | |
| 27 | 8 | Some name | 11.2417340191036056 | 11.2417340191036056 | 11.2417340191036056 | | | | |
| 28 | 8 | Another | 9.9926524614254272 | 9.9926524614254272 | 9.9926524614254272 | | | | |
| 29 | 10 | Stuff | 7.4944893460690704 | 7.4944893460690704 | 7.4944893460690704 | | | | |
+----+-------+--------------------+---------------------+---------------------+---------------------+------+------+------+---
