Number of occupied days in a year from reservation table

Question

I have read this question but it is not really helping me. I have a similar system of booking for homes. So everytime somebody needs to rent a house, he just logs on and select a time where the house he wants is free, and books it. So in my case, I also have a start_time and end_time. On my records I have precised time (datetime format) for each date so I can calculate date differences based on time precicion. Here is a sample of my database :

CREATE TABLE IF NOT EXISTS `bookings` (
  `id_booking` int(11) NOT NULL AUTO_INCREMENT,
  `id_user_staff` int(11) NOT NULL,
  `start_date` datetime NOT NULL,
  `end_date` datetime NOT NULL,
  `details` text,
  `statut` varchar(45) DEFAULT NULL,
  `id_house` int(1) NOT NULL,
  `fees` decimal(6,0) DEFAULT NULL,
  `ip_user` varchar(15) NOT NULL,
  `date_booked` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_booking`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `users` (
  `id_user` int(5) NOT NULL DEFAULT '0',
  `Last_Name` varchar(23) DEFAULT NULL,
  `First_Name` varchar(23) DEFAULT NULL,
  `Job_Title` varchar(56) DEFAULT NULL,
  `Status` varchar(19) DEFAULT NULL,
  `Company` varchar(38) DEFAULT NULL,
  `Mobile` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id_user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `house` (
  `id_house` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `details` text,
  PRIMARY KEY (`id_house`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

My need now, is to determine how occupied is a home over a year, a precision based on number of days included in bookings for that home. That percentage is a ratio base on the number of days of booking over the number of days in the year. What will be my query to get the number of days and trying to avoid the issue of the similar question which count twice some days?

it sounds like you already have a query that returns some unwanted values? Mind to share that with us? — Olli, Jul 31 '15 at 10:34

Jens · Accepted Answer · 2015-07-31T10:48:18.977

You can use DATEDIFF, which gives you the number of days between two dates. But since DATEDIFF('2015-01-01', '2015-01-01') results in 0 days, you have to add one day to the result.

Afterwards, sum up all the diffs for each house:

SELECT id_house, SUM(DATEDIFF(end_date, start_date) + 1) 
FROM bookings 
GROUP BY id_house

Of course, a house which is booked from 2015-01-01 to 2015-01-02 AND from 2015-01-02 to 2015-01-03 will result in three booked days because one day is booked twice. You can substract these double bookings afterwards:

SELECT id_house, COUNT(*) 
FROM bookings b1
WHERE (id_house, date(start_date)) IN 
( SELECT id_house, date(end_date) FROM bookings AS b2 WHERE b1.id_booking <> b2.id_booking )
ORDER BY id_house

This gives you the number of days which are start_date and end_date (in two different bookings) at the same time.

In the end, I would use a query like the following, which sums it all up:

SELECT bookingdays.id_house, bookingdays.days, COALESCE(doublebookings.doubledays, 0), bookingdays.days - COALESCE(doublebookings.doubledays, 0) AS totaldays
FROM
(SELECT id_house, SUM(DATEDIFF(end_date, start_date) + 1) AS days
FROM bookings 
GROUP BY id_house) bookingdays
LEFT OUTER JOIN
(SELECT id_house, COUNT(*) AS doubledays
FROM bookings b1
WHERE (id_house, date(start_date)) IN 
( SELECT id_house, date(end_date) FROM bookings AS b2 WHERE b1.id_booking <> b2.id_booking )
ORDER BY id_house) doublebookings
ON bookingdays.id_house = doublebookings.id_house

Bulat · Answer 2 · 2015-07-31T11:13:49.413

The best way to work with dates in reporting domain is using Date dimension:

-- Source:
-- http://www.joyofdata.de/blog/setting-up-a-time-dimension-table-in-mysql/
SET @d0 = "2000-01-01";
SET @d1 = "2020-01-01";

SET @date = date_sub(@d0, interval 1 day);

# set up the time dimension table
DROP TABLE IF EXISTS date_dimension;
CREATE TABLE `date_dimension` (
  `date` date DEFAULT NULL,
  `id` int NOT NULL,
  `y` smallint DEFAULT NULL,
  PRIMARY KEY (`id`)
);

# populate the table with dates
INSERT INTO date_dimension
SELECT @date := date_add(@date, interval 1 day) as date,
    # integer ID that allows immediate understanding
    date_format(@date, "%Y%m%d") as id,
    year(@date) as y
FROM T
WHERE date_add(@date, interval 1 day) <= @d1
ORDER BY date
;

Once you have date dimension table you can join your bookings to it and count unique dates.

Days per year:

SELECT year, COUNT(*) FROM date_dimension GROUP BY YEAR;

Days booked per year and property:

SELECT year, house_id, COUNT(DISTINCT d.date)
FROM date_dimension d INNER JOIN bookings b
   ON d.date BETWEEN b.start_date AND b.end_date
GROUP BY year, house_id

You can modify first query to become a subquery to get percentages:

SELECT d.year, b.house_id,
  COUNT(DISTINCT d.date) / 
  (SELECT COUNT(*) 
   FROM date_dimension WHERE year =d.year) AS annual_percent
FROM date_dimension d INNER JOIN bookings b
   ON d.date BETWEEN b.start_date AND b.end_date
GROUP BY year, house_id

You will notice in your work that date related calculations will take fewer lines of SQL with a much more explicit statements if date dimension is used.

Number of occupied days in a year from reservation table

2 Answers2