Find all records where between 2 dates have a certain value for each record

Question

I have a table with vacation houses which have some availability (column value, value 1 means available ). How can I find all houses (column unit_id) that are are available between 2 dates.

table

CREATE TABLE `houseavailability` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` varchar(100) DEFAULT NULL,
  `value` varchar(100) DEFAULT NULL,
  `unit_id` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `houseavailability_unit_id_IDX` (`unit_id`,`date`) USING BTREE,
  KEY `houseavailability_unit_id_IDX_solo` (`unit_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=16648943 DEFAULT CHARSET=latin1;

test data

INSERT INTO houseavailability
(id, `date`, value, unit_id)
VALUES(15814115, '2022-07-23', '1', '1004004');
INSERT INTO houseavailability
(id, `date`, value, unit_id)
VALUES(15814116, '2022-07-24', '1', '1004004');
INSERT INTO houseavailability
(id, `date`, value, unit_id)
VALUES(15814117, '2022-07-25', '1', '1004004');
INSERT INTO houseavailability
(id, `date`, value, unit_id)
VALUES(15814118, '2022-07-26', '1', '1004004');
INSERT INTO houseavailability
(id, `date`, value, unit_id)
VALUES(15814119, '2022-07-27', '1', '1004004');
INSERT INTO houseavailability
(id, `date`, value, unit_id)
VALUES(15814120, '2022-07-28', '1', '1004004');
INSERT INTO houseavailability
(id, `date`, value, unit_id)
VALUES(15814121, '2022-07-29', '1', '1004004');
INSERT INTO houseavailability
(id, `date`, value, unit_id)
VALUES(15814122, '2022-07-30', '0', '1004004');

attempt

SELECT houseavailability.*
FROM houseavailability
WHERE houseavailability.date BETWEEN '2022-07-23' AND '2022-07-30'
AND houseavailability.unit_id = 1004004;

http://sqlfiddle.com/#!9/094547/2

Answer 1

For example, find a unit_id which is available during the whole specified period.

SELECT unit_id
FROM houseavailability
WHERE date BETWEEN '2022-07-23' AND '2022-07-30'
GROUP BY unit_id
HAVING sum(value) = datediff('2022-07-30','2022-07-23') + 1;