rafael beckert rafael beckert - 7 months ago 24
SQL Question

SQL Query with double distinct dates iteration, and start date < my date < final date

I have a kind of rental system database where user can rent an entire house, or just a room of the house.

I have a table called

offers
which has columns
id
,
room_id
and a few more.

If
room_id = NULL
, it refers to an entire house.

I have a table called
availability
which has columns
offer_id
,
room_id
,
date
,
status
(available, unavailable)

If
room_id = NULL
, it refers to an availability of an entire house.

select `offer_id` , `room_id`
from `availability`
where `date` > CAST('2016-05-17' as date)
and `date` <= CAST('2016-05-21' as date)
and `status` = 'available'
group by `offer_id`
having COUNT(DISTINCT `date`) = DATEDIFF('2016-05-21', '2016-05-17')


Ok, but my problem is: if a room is unavailable at day 20 but the house have another room available at day 20 the query will return a false and indistinct select. I need all those availability where room_id is null(an entire house), and a separated result where room_id is not null and distinct when compare the dates for each offer_id (offer_id= 1 and room_id = 1, offer_id = 1 and room_id = 2 ...)

SAMPLE DATA:

http://sqlfiddle.com/#!9/f5dfe

CREATE TABLE `availability` (
`offer_id` int(10) UNSIGNED NOT NULL,
`room_id` int(10) UNSIGNED DEFAULT NULL,
`date` date NOT NULL,
`status` enum('available','UNAVAILABLE') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'available'
);

INSERT INTO `availability` (`offer_id`, `room_id`, `date`, `status`) VALUES
(1, NULL, '2016-05-18', 'UNAVAILABLE'),
(1, NULL, '2016-05-19', 'available'),
(1, NULL, '2016-05-20', 'available'),
(1, NULL, '2016-05-21', 'available'),
(1, 1, '2016-05-18', 'available'),
(1, 1, '2016-05-19', 'UNAVAILABLE'),
(1, 1, '2016-05-20', 'available'),
(1, 1, '2016-05-21', 'available'),
(1, 2, '2016-05-18', 'available'),
(1, 2, '2016-05-19', 'UNAVAILABLE'),
(1, 2, '2016-05-20', 'available'),
(1, 2, '2016-05-21', 'available'),
(1, 3, '2016-05-18', 'available'),
(1, 3, '2016-05-19', 'available'),
(1, 3, '2016-05-20', 'UNAVAILABLE'),
(1, 3, '2016-05-21', 'available');


using the query above will give me one result (offer_id = 1), but the correct is no results.
because none entire house (room_id = null) or a room is available when search the dates where all dates appear available between start date and final date

Answer

Updated as initial answer was incorrect: The following fiddle has more data: http://sqlfiddle.com/#!9/d6602/1/0

SELECT `Offer_id`, `room_id`
from
    ( select `offer_id`, `room_id`
    from `availability` 
    where `date` > CAST('2016-05-17' as date) 
      and `date` <= CAST('2016-05-21' as date) 
      and `status` = 'available'
    group by `offer_id`, `room_id`
    having COUNT(DISTINCT `date`) = DATEDIFF('2016-05-21', '2016-05-17')) As HousesAndRooms
WHERE NOT `room_id` IS NULL OR (`room_id` is null AND`offer_id` NOT IN(
    ( select `offer_id`
    from `availability` 
    where `date` > CAST('2016-05-17' as date) 
      and `date` <= CAST('2016-05-21' as date) 
      and `status` = 'UNAVAILABLE'
      and not `room_id` is null
    group by `offer_id`, `room_id`
    having COUNT(DISTINCT `date`) > 0 ) 
  ) )

The query above selects all available offers (houses and rooms) for a date range, where room_id is null (i.e. a whole house) it will check if there are any unavailable rooms (not room_id is null) for a given date range.