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

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

CodePudding user response：

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;

CodePudding user response：

You can try to use the condition aggregate function in HAVING to compare whether all the rows for which this is true between your date condition.

Query 1:

SELECT unit_id
FROM houseavailability
WHERE date BETWEEN '2022-07-23' AND '2022-07-30'
GROUP BY unit_id
HAVING COUNT(DISTINCT date) = COUNT(DISTINCT CASE WHEN value = '1' THEN date END)

Results:

DISTINCT which is in aggregate function will count only once, if there are duplicate days have 1 value in your tables, but if you want to count multiple when you met that situation you can remove DISTINCT from the aggregate function.

EDIT

Due to there being a UNIQUE constraint from your unit_id and date columns, you don't need to use DISTINCT on your aggregate function.

SELECT unit_id
FROM houseavailability
WHERE date BETWEEN '2022-07-23' AND '2022-07-30'
GROUP BY unit_id
HAVING COUNT(*) = COUNT(CASE WHEN value = '1' THEN date END)