2

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')
  • I don't really understand what you're trying to do or what you're asking. Could you clarify your question, pehaps with an example of what the query should return, based on your example data? Use the "edit" button in the lower-left corner of your question. – Daniel Hutmacher Feb 9 '16 at 14:45
2

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
  • what I need, is the oferta_id that have all days between startDate and finalDate – RBK Feb 9 '16 at 19:23
  • If I need the oferta_id that have all days between StartDate and finalDate, and all these days have to be status 1 – RBK Feb 9 '16 at 19:42
  • @rafael beckert the answer now include all the option (unique date or not) and with or without status = X – Julien Vavasseur Feb 10 '16 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.