MySQL count N consecutive days in normal form tables

Question

I want to count N consecutive days that a specific user has meetings, on a given date and before it.

For example: count the consecutive meeting days that a user with id 1 has at 16 January 2013.

I found some good answers here and here but the tables are not in normal form like my sample above and i cannot figure out how to implement it for my occasion.

A sample table structure as follows:

CREATE TABLE IF NOT EXISTS `meetings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `meetings_users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `meeting_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `meeting_id` (`meeting_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

--
-- Constraints for table `meetings_users`
--
ALTER TABLE `meetings_users`
  ADD CONSTRAINT `meetings_users_ibfk_2` FOREIGN KEY (`meeting_id`) REFERENCES `meetings` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `meetings_users_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Sample inserts

INSERT INTO  `users` ( `id` ) VALUES (1)

INSERT INTO `meetings` ( `id`, `time` ) VALUES 
(1, '2013-01-14 10:00:00'), 
(2, '2013-01-15 10:00:00'), 
(3, '2013-01-16 10:00:00')


INSERT INTO `meetings_users` ( `id`, `meeting_id`, `user_id` ) VALUES 
(1, 1, 1), 
(2, 2, 1), 
(3, 3, 1)

Desired output:

*+---------+-----------------+
| user_id | consecutive_days |
+---------+------------------+
|       1 | 3                |
+---------+------------------+

Consider providing proper DDLs for the above, including some inserts and the desired result. — Strawberry, Jan 14 '13 at 11:57
Better? Yes, but it's still not really representative is it! Come on, give us a little more... — Strawberry, Jan 14 '13 at 12:14
Sorry forgot to add the code for foreign keys. Added them now. The keys are foreign indeed. — Vassilis Barzokas, Jan 14 '13 at 12:27

Strawberry · Accepted Answer · 2013-01-14T14:13:43.980

How about something like this. I expect it can be re-written without the subqueries but I must be having a bit of a brain freeze... (data set and query amended to suit shifting requirements)

DROP TABLE IF EXISTS meetings;
CREATE TABLE IF NOT EXISTS meetings 
( meeting_id int(10) unsigned NOT NULL AUTO_INCREMENT
, meeting_time datetime NOT NULL
, PRIMARY KEY (meeting_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS meetings_users;
CREATE TABLE IF NOT EXISTS meetings_users 
( user_id int(10) unsigned NOT NULL
, meeting_id int(10) unsigned NOT NULL
, PRIMARY KEY (meeting_id,user_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS users;
CREATE TABLE IF NOT EXISTS users 
( user_id int(10) unsigned NOT NULL AUTO_INCREMENT
, PRIMARY KEY (user_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

INSERT INTO  users ( user_id ) VALUES (1),(2),(3),(4);

INSERT INTO meetings ( meeting_id, meeting_time ) VALUES 
(1, '2013-01-14 10:00:00'), 
(2, '2013-01-15 10:00:00'), 
(3, '2013-01-16 10:00:00'),
(4, '2013-01-17 10:00:00'),
(5, '2013-01-18 10:00:00'),
(6, '2013-01-19 10:00:00'),
(7, '2013-01-20 10:00:00'),
(8, '2013-01-14 12:00:00');


INSERT INTO meetings_users (meeting_id, user_id ) VALUES 
(1, 1), 
(2, 1),
(2, 3),
(3, 1),
(3, 3),
(4, 2),
(4, 3), 
(5, 2), 
(6, 1),
(1, 8);

SET @dt = '2013-01-15';

SELECT user_id
     , start
     , DATEDIFF(@dt,start)+1 cons
  FROM
     (
       SELECT a.user_id
            , a.meeting_date Start
            , MIN(c.meeting_date) End
        , DATEDIFF(MIN(c.meeting_date),a.meeting_date)  + 1 diff
         FROM (SELECT DISTINCT mu.user_id,DATE(m.meeting_time) meeting_date FROM meetings_users mu JOIN meetings m ON m.meeting_id = mu.meeting_id) a
         LEFT
         JOIN (SELECT DISTINCT mu.user_id,DATE(m.meeting_time) meeting_date FROM meetings_users mu JOIN meetings m ON m.meeting_id = mu.meeting_id) b
       ON b.user_id = a.user_id
          AND a.meeting_date = b.meeting_date + INTERVAL 1 DAY
         LEFT
         JOIN (SELECT DISTINCT mu.user_id,DATE(m.meeting_time) meeting_date FROM meetings_users mu JOIN meetings m ON m.meeting_id = mu.meeting_id) c
       ON c.user_id = a.user_id
          AND a.meeting_date <= c.meeting_date
         LEFT
         JOIN (SELECT DISTINCT mu.user_id,DATE(m.meeting_time) meeting_date FROM meetings_users mu JOIN meetings m ON m.meeting_id = mu.meeting_id) d
           ON d.user_id = a.user_id
          AND c.meeting_date = d.meeting_date - INTERVAL 1 DAY
        WHERE b.meeting_date IS NULL
      AND c.meeting_date IS NOT NULL
          AND d.meeting_date IS NULL
        GROUP
       BY a.user_id
        , a.meeting_date
     ) x
 WHERE @dt BETWEEN start AND end;
 +---------+------------+------+
 | user_id | start      | cons |
 +---------+------------+------+
 |       1 | 2013-01-14 |    2 |
 |       3 | 2013-01-15 |    1 |
 +---------+------------+------+

Seems to miscount when there are multiple meetings on one day. — Mike Sherrill 'Cat Recall', Jan 14 '13 at 13:43
That could easily be fixed with a DISTINCT in each of the subqueries — Strawberry, Jan 14 '13 at 13:48
+1 Catcall . Also i want to specify only one date for which to get the consecutive meetings. I tried with HAVING clause, but no luck. Pretty close though — Vassilis Barzokas, Jan 14 '13 at 13:48
So, given my data set, a search for user_id 1 on 15th Jan would equal '2', right? — Strawberry, Jan 14 '13 at 13:58
Thanks Strawberry your solution is working fine. I just realized that i do not need to count weekends(Saturday,Sunday) and skip them.. So i guess i will have to open another question huh? — Vassilis Barzokas, Jan 14 '13 at 17:41
I (or someone else) might be able to amend this response to suit... if you can wait a couple of hours. — Strawberry, Jan 14 '13 at 19:51
I was just about to post an amendment but I spotted some errors, proving that the problem's tricker than I first thought. Maybe it is worth starting up a new question after all! — Strawberry, Jan 15 '13 at 01:20

MySQL count N consecutive days in normal form tables

1 Answers1

Linked