1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

mysql query which needs to do calculations

Discussion in 'MySQL' started by barricades, Jul 31, 2013.

0
  1. #1
    I'm not so great with sql but I think what I want should be possible, I'm just not sure how to do it.

    I have a site which allows users to list their youth hostel. Potential customers can then browse these hostels and place bookings.

    Tables: (not including id's and other cols!)
    hostels
    -------
    max_guests

    bookings
    -------
    hostel_id
    check_in
    check_out
    guests

    When users search the site I allow them to select a checkin date, a checkout date, a place name, and the number of guests. Note that each hostel has a maximum number of guests which they can accommodate, for example 20.

    So say previously a booking has been made at a particular hostel for 8 people during the dates selected for the search, then I need to know that the maximum number of guests that particular hostel can accommodate between those dates is now 12, and it would only appear in searches for <= 12 guests.

    Likewise if that hostel had a couple more bookings and was at it's maximum capacity, then it shouldn't turn up in the search at all.

    At the moment I have a query which gets all the hostels but if there's any bookings for a hostel between the selected dates, then it's excluded from the results. I'd like to be able to get all the bookings for each hostel during the selected dates, count the numbers of guests booked in, and if that number subtracted from the max_guests of the hostel gives a result which is lower than the number of guests specified in the search, then exclude it from the results.

    at the moment my query looks like:
    SELECT DISTINCT `Hostel`.`id`, `Hostel`.*, `Hostel`.`id`
    FROM `hostelsdatabase`.`hostels` AS `Hostel`
    LEFT JOIN `hostelsdatabase`.`bookings` AS `bookings` ON (`bookings`.`hostel_id` = `Hostel`.`id`)
    LEFT JOIN `hostelsdatabase`.`users` AS `User` ON (`Hostel`.`user_id` = `User`.`id`)
    WHERE ((`Hostel`.`address_one` LIKE '%london%') OR (`Hostel`.`address_two` LIKE '%london%') OR (`Hostel`.`city` LIKE '%london%'))
    AND `Hostel`.`maxguests` >= 4
    AND NOT EXISTS (SELECT * FROM bookings WHERE `bookings`.`hostel_id` = `Hostel`.`id` AND NOT('2013-07-31' > `bookings`.`checkout` OR '2013-08-02' < `bookings`.`checkin`))
    LIMIT 10

    Can anyone tell me how I could modify that query to make it do what I need?
     
    barricades, Jul 31, 2013 IP
  2. barricades

    barricades Member

    Messages:
    90
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    43
    #2
    Hey there,

    In case it encourages anyone to help me out I've stuck together a sqlfiddle: http://sqlfiddle.com/#!2/300f3/1

    and a better description of my problem might be:

    two tables: Hostels and Bookings.

    Hostels have a maximum number of guests which they can accommodate. Bookings are made between two dates and have a column 'guests' for the number of guests in the booking.

    Users should be able to search for a hostel given an area, a checkin date, a checkout date and the number of guests in the party.

    My query at the moment finds all the hostels which don't have a booking between the dates but what I actually want is:

    The query to return all hostels where, if the hostel has bookings during the user specified period, then the sum of the guests involved in those bookings, subtracted from hostel.maxguests shows that there is enough space left in the hostel. IE the number needs to be more than the user specified guests number from the search.
     
    barricades, Aug 1, 2013 IP
    sarahk likes this.
  3. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #3
    I didn't realise someone had created a sqlfiddle - brilliant find on your part!

    I'd start out by creating a dates table
    create table if not exists `dates` (
  `id` int(11) not null auto_increment,
  `date` date default null,
  primary key (`id`)
  );
insert into `dates` (`id`,`date`) values
(1, '2013-08-01'),
(2, '2013-08-02'),
(3, '2013-08-03'),
(4, '2013-08-04'),
(5, '2013-08-05'),
(6, '2013-08-06'),
(7, '2013-08-07'),
(8, '2013-08-08'),
(9, '2013-08-09'),
(10, '2013-08-10'),
(11, '2013-08-11'),
(12, '2013-08-12'),
(13, '2013-08-13'),
(14, '2013-08-14'),
(15, '2013-08-15'),
(16, '2013-08-16'),
(17, '2013-08-17'),
(18, '2013-08-18'),
(19, '2013-08-19'),
(20, '2013-08-20'),
(21, '2013-08-21'),
(22, '2013-08-22'),
(23, '2013-08-23'),
(24, '2013-08-24'),
(25, '2013-08-25'),
(26, '2013-08-26'),
(27, '2013-08-27'),
(28, '2013-08-28'),
(29, '2013-08-29'),
(30, '2013-08-30')
    Code (markup):
    which then lets you run this query 
    select dates.date, hostels.id, hostels.room_type,
sum(bookings.guests) as guests, 
hostels.maxguests - sum(case when isnull(bookings.guests) then 0 else bookings.guests end) as capacity,
hostels.maxguests
from dates
cross join hostels
left join bookings on (dates.date >= bookings.checkin and dates.date <= bookings.checkout and bookings.hostel_id = hostels.id)
group by dates.date, hostels.id having capacity > 4
    Code (markup):
    which shows you for every day of the month which hostels have the capacity to take in the number of guests your enquiry is for.
     
    sarahk, Aug 2, 2013 IP