0

I have an apartment booking system and need to generate a report of number of occcupied days vs. unoccupied days within a date period on a property by property basis.

Bearing in mind within the chosen period that some bookings may start before and/or end after the start / end date respectivley.

I found this - MySQL Number of Days inside a DateRange, inside a month (Booking Table) - which is along the right lines, but I don't want it on a fixed, month by month basis but rather between two variable dates.

Ideally I'd like to do this using MySQL only but if PHP is needed then that is fine.

The only way I could think to do it would be to loop through each day individually and check if there was an occupancy on that day, but that just seems incredible inefficient.

Edit: I managed to adapt the code from the other questions as follows:

CREATE TABLE IF NOT EXISTS `view_bookings` (
`bkg_id` int(11) NOT NULL AUTO_INCREMENT,
`apt_id` int(10) NOT NULL,
`apt_name` varchar(50) NOT NULL,
`start_date` date DEFAULT NULL,
`end_date` date DEFAULT NULL,
PRIMARY KEY (`bkg_id`),
UNIQUE KEY `bkg_id_UNIQUE` (`bkg_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO `view_bookings` (`apt_id`, `apt_name`, `start_date`, `end_date`) VALUES
(1, 'Apartment One', '2012-09-02', '2013-02-05'),
(1, 'Apartment One', '2013-02-05', '2013-07-05'),
(2, 'Apartment Two', '2012-12-25', '2013-02-28'),
(2, 'Apartment Two', '2013-03-01', '2013-04-10'),
(2, 'Apartment Two', '2013-04-16', '2013-09-19'),
(3, 'Apartment Three', '2013-01-01', '2013-02-04'),
(3, 'Apartment Three', '2013-02-06', '2013-02-12'),
(3, 'Apartment Three', '2013-02-16', '2013-02-27'),
(3, 'Apartment Three', '2013-02-27', '2013-03-14'),
(3, 'Apartment Three', '2013-03-19', '2013-06-12');

SELECT
SUM(
    1 + DATEDIFF(
        LEAST(end_date, '2013-03-30'),
        GREATEST(start_date, '2013-02-01')
        )
    ) AS days,
    apt_name,
    apt_id
FROM
    view_bookings
WHERE
    start_date <= '2013-03-30'
AND '2013-02-01' <= end_date
GROUP BY
    apt_id

This works, however if there are overlapping bookings then it counts days twice. How can I prevent this?

Community
  • 1
  • 1
Pandy Legend
  • 1,102
  • 3
  • 15
  • 29
  • 1
    Can you post the table declares of the relevant tables and some example data / results? – Kickstart Jul 10 '13 at 11:36
  • Done - see edited question above. – Pandy Legend Jul 10 '13 at 11:52
  • 1
    I guess you should work with PHP. You can put all dates within a booking period (so between `start_date` and `end_date`) in a array, use `array_unique` and `count` to find out the amount of bookings without overlappig items. – Pieter Jul 10 '13 at 12:05

2 Answers2

1

Possibly do it by generating all the dates between the start and end dates, using distinct to remove the duplicate dates. Do a count of that to get the total number of unique booked dates in the relevant range. Then use that figure with the count of days within the date range to get the number of free days.

Done for each apartment:-

SELECT apt_id, apt_name, DaysBooked AS DaysOccupied, DayNumber - DaysBooked AS DaysUnoccupied
FROM
(
    SELECT apt_id, apt_name, COUNT(*) AS DaysBooked
    FROM
    (
        SELECT DISTINCT view_bookings.apt_id, view_bookings.apt_name, DATE_ADD(view_bookings.start_date, INTERVAL units.i + tens.i * 10 + hundreds.i * 100 DAY) AS BookedDate
        FROM view_bookings
        CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
        CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
        CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) hundreds
        WHERE DATE_ADD(view_bookings.start_date, INTERVAL units.i + tens.i * 10 + hundreds.i * 100 DAY) <= view_bookings.end_date
        AND DATE_ADD(view_bookings.start_date, INTERVAL units.i + tens.i * 10 + hundreds.i * 100 DAY) BETWEEN '2013-02-01' AND '2013-02-28'
    ) Sub1
    GROUP BY apt_id, apt_name
) Sub3
CROSS JOIN 
(
    SELECT ABS(DATEDIFF('2013-02-01', '2013-02-28')) + 1 AS DayNumber -- Note that DATEDIFF is giving the difference in days but you want the figure to include the start and end dates so add 1.
) Sub2

Note that this in only coping with date ranges of up to 1000 days, but easily expanded to cover more.

Kickstart
  • 21,403
  • 2
  • 21
  • 33
  • This doesn't produce the correct result. Shows 0 for apartments One and Two and 4 for Apartment Three. [link](http://sqlfiddle.com/#!2/72a14/7/0) – Pandy Legend Jul 10 '13 at 13:23
  • 1
    From your data those are the correct results for the number of unoccupied days for each of those apartments. Just modified it to specifically give you a column of the occupied and unoccupied days for each apartment – Kickstart Jul 10 '13 at 13:39
1

Try this. If you use PHP, drop first two lines and use php variables inside the SQL statment

SET @START = '2013-02-01';
SET @END = '2013-03-30';
SELECT vb.apt_id, SUM(1 + DATEDIFF(LEAST(vb.end_date, @END), GREATEST(vb.start_date, @START))) AS Days 
FROM view_bookings vb
WHERE (vb.start_date BETWEEN @START AND @END AND vb.end_date BETWEEN @START AND @END) 
OR (vb.start_date < @START AND vb.end_date BETWEEN @START AND @END)
OR (vb.start_date BETWEEN @START AND @END AND vb.end_date > @END) 
OR (vb.start_date < @START AND vb.end_date > @END) 
GROUP BY vb.apt_id
Mirko
  • 757
  • 9
  • 13
  • This doesn't produce results any differently from my code. Double-bookings are still counted twice. – Pandy Legend Jul 10 '13 at 13:24
  • 1
    Do you mean when there is a checkout and checkin the same day? – Mirko Jul 12 '13 at 07:14
  • Yes, or if a second person moves in to the same apartment for a period of time. (i.e. there can be multiple bookings at the same time for one apartment but the statistics should only show occupied or not) – Pandy Legend Jul 12 '13 at 11:18