MySQL – How to Search Between Dates Where All Dates Appear

dateMySQLsubquery

I want to search house_id where all days appear within a range of 2 dates from startDate to finalDate.

Tables columns are:

  • id
  • house_id
  • date
  • status

house_id + date are unique

the closest I could get is:

SELECT * FROM (
SELECT *
FROM tests) AS T1
WHERE date BETWEEN '2016-02-01' AND '2016-02-02'
HAVING COUNT(*) = DATEDIFF('2016-02-02', '2016-02-01') < 1

sample data:

CREATE TABLE IF NOT EXISTS `tests` ( 
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`),
`house_id` int(10) unsigned NOT NULL,
`date` date NOT NULL,
`status` enum('0','1','2') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `tests` (`id`, `house_id`, `date`, `status`) VALUES
(1,1, '2016-02-01', '1'),
(2,1, '2016-02-02', '1'),
(3,1, '2016-02-03', '1'), 
(4,1, '2016-02-04', '1'), 
(5,1, '2016-02-05', '1'), 
(6,1, '2016-02-06', '1'), 
(7,2, '2016-02-01', '2'), 
(8,2, '2016-02-02', '2'), 
(9,2, '2016-02-04', '2'), 
(10,2, '2016-02-05', '2'), 
(11,2, '2016-02-06', '2'), 
(12,2, '2016-02-07', '2'), 
(13,2, '2016-02-08', '2')

Best Answer

I believe you want to get house_id where date exists for each day between @startDate and @finalData.

Sample queries with 3 different house_id: SQL Fiddle.

Query:

SET @startDate := CAST('2016-02-01' as date);
SET @finalDate := CAST('2016-02-04' as date);

SELECT house_id
    , COUNT(*)
    , DATEDIFF(@startDate, @finalDate)
FROM @tests AS T1
WHERE `date` > @startDate AND `date` <= @finalDate
GROUP BY house_id;

Query 1 in SQL Fiddle.

This query GROUP BY house_id and output the COUNT of days between @startDate and @finalData:

house_id    COUNT(*)    DATEDIFF(@startDate, @finalDate)
1           3                       3
2           2                       3
3           3                       3

From this query, it is obvious that only rows where COUNT(*) = DATEDIFF(@startDate, @finalDate) should be returned.

If (house_id, date) is not unique, COUNT(*) must be replaced by COUNT(DISTINCT `date`).

Query with all days

If you only want house_id where COUNT(*) = DATEDIFF(day, @startDate, @finalDate), the query must use the HAVING clause:

SELECT house_id
    , COUNT(*)
FROM @tests AS T1
WHERE `date` > @startDate AND `date` <= @finalDate
GROUP BY house_id
HAVING COUNT(*) = DATEDIFF(@finalDate, @startDate)

Query 2 in SQL Fiddle.

Output:

house_id    COUNT(*)
1           3
3           3

Query where status = X

Status value can be checked in the WHERE clause:

SELECT house_id
    , COUNT(*)
FROM @tests AS T1
WHERE `date` > @startDate AND `date` <= @finalDate
    AND status = '1'
GROUP BY house_id
HAVING COUNT(*) = DATEDIFF(@finalDate, @startDate)

Query 3 in SQL Fiddle.

Output:

house_id    COUNT(*)
1           3
Related Question