1
 CREATE TABLE `rings` (
`ID_RingType` char(2) default NULL,
`Number` date default NULL,
`ID_User` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `rings` (`ID_RingType`, `Number`, `ID_User`) VALUES 
('AA', '2018-09-01', 1),
('AA', '2018-09-02', 1),
('AA', '2018-09-03', 1),
('AA', '2018-09-11', 1),
('AA', '2018-09-12', 1),
('AA', '2018-09-13', 1),
('AA', '2018-09-14', 1),
('AA', '2018-09-15', 1),
('AB', '2018-09-16', 1),
('AB', '2018-09-17', 1),
('AB', '2018-09-18', 1),
('AB', '2018-09-19', 1),
('AB', '2018-09-20', 2),
('AB', '2018-09-21', 2),
('AB', '2018-09-22', 2);

I wish to group the data based on ID_User and ID_RingType and for each contiguous range of dates list the MIN and MAX.

The results should look like:

  ID_User   ID_RingType    MIN( Number )    MAX( Number )
       1      AA            2018-09-01        2018-09-03
       1      AA            2018-09-11        2018-09-15
       1      AB            2018-09-16        2018-09-19
       2      AB            2018-09-20        2018-09-22

This is what i tried :

      SET @grp = NULL ;# MySQL returned an empty result set (i.e. zero 
       rows).
      # MySQL returned an empty result set (i.e. zero rows).
      # MySQL returned an empty result set (i.e. zero rows).
       SET @preNum = NULL ;# MySQL returned an empty result set (i.e. zero 
      rows).
      # MySQL returned an empty result set (i.e. zero rows).
      # MySQL returned an empty result set (i.e. zero rows).
      SELECT ID_User, ID_RingType, MIN( Number ) , MAX( Number ) 
     FROM (

     SELECT ID_RingType, ID_User, Number, @grp := 
     CASE WHEN Number = DATE_ADD( @preNum , INTERVAL 1 
     DAY ) 
     THEN @grp 
     ELSE DATE_ADD( @grp , INTERVAL 1 
     DAY ) 
     END grp, @preNum := Number
     FROM  `Rings` 
     ORDER BY ID_RingType, ID_User, Number
     )T
     GROUP BY ID_RingType, ID_User, grp

Any Help appreciated.

  • considering running a search on 'contiguous data' => should provide quite a few Q&A sessions that address this topic – markp Sep 30 '18 at 17:42
  • This question looks too much like the 'dup' question; are you asking something different?? – Rick James Oct 12 '18 at 18:41

Browse other questions tagged or ask your own question.