sql to group adjacent rows

This is a follow up to http://www.experts-exchange.com/Database/MySQL/Q_22924454.html

I have a database which logs patient visits. It is important to know when patients return within 3 days of an initial visit, so those charts can be reviewed. Sometimes, a patient will return once. Sometimes they will return several times. I need to be able to capture these returns when they happen and assign the group of visits a unique id for quality review.

So, I have a table like this with arbitrary data:

DROP TABLE IF EXISTS `tmp`;
CREATE TABLE `tmp` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `arrival` datetime NOT NULL,
  `mrn` varchar(45) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;

LOCK TABLES `tmp` WRITE;
/*!40000 ALTER TABLE `tmp` DISABLE KEYS */;
INSERT INTO `tmp` VALUES (1,'2007-01-01 00:00:00','a'),(2,'2007-01-01 00:00:00','b'),(3,'2007-01-01 00:00:00','c'),(4,'2007-01-02 00:00:00','a'),(5,'2007-01-02 00:00:00','d'),(6,'2007-01-02 00:00:00','e'),(7,'2007-01-04 00:00:00','a'),(8,'2007-01-04 00:00:00','g'),(9,'2007-01-04 00:00:00','h'),(10,'2007-01-06 00:00:00','a'),(11,'2007-01-06 00:00:00','j'),(12,'2007-01-06 00:00:00','k'),(13,'2007-01-08 00:00:00','a'),(14,'2007-01-08 00:00:00','o'),(15,'2007-01-09 00:00:00','p'),(16,'2007-01-11 00:00:00','p'),(17,'2007-01-11 00:00:00','q'),(18,'2007-02-01 00:00:00','a'),(19,'2007-02-03 00:00:00','a'),(20,'2007-01-02 00:00:00','c');
/*!40000 ALTER TABLE `tmp` ENABLE KEYS */;
UNLOCK TABLES;

I have a great query which will find all cases that had recent visits:

SELECT T1.mrn, T1.id, T1.arrival
FROM tmp AS T1
INNER JOIN tmp AS T2
ON T1.arrival BETWEEN DATE_SUB(T2.arrival, INTERVAL 3 DAY) AND DATE_ADD(T2.arrival, INTERVAL 3 DAY)
AND T1.mrn = T2.mrn
AND T1.id <> T2.id
GROUP BY T1.mrn,t1.id

which produces this:

mrn, id, arrival
'a', 1, '2007-01-01 00:00:00'
'a', 4, '2007-01-02 00:00:00'
'a', 7, '2007-01-04 00:00:00'
'a', 10, '2007-01-06 00:00:00'
'a', 13, '2007-01-08 00:00:00'
'a', 18, '2007-02-01 00:00:00'
'a', 19, '2007-02-03 00:00:00'
'c', 3, '2007-01-01 00:00:00'
'c', 20, '2007-01-02 00:00:00'
'p', 15, '2007-01-09 00:00:00'
'p', 16, '2007-01-11 00:00:00'

Now what I need to do is make a new query which will assign each chain of adjacent visits a unique review number. For example, a result like this would be nice:

reviewid, mrn, id, arrival
1, 'a', 1, '2007-01-01 00:00:00'
1, 'a', 4, '2007-01-02 00:00:00'
1, 'a', 7, '2007-01-04 00:00:00'
1, 'a', 10, '2007-01-06 00:00:00'
1, 'a', 13, '2007-01-08 00:00:00'
2, 'a', 18, '2007-02-01 00:00:00'    <--- note that this is a new review number because >3 days since last visit
2, 'a', 19, '2007-02-03 00:00:00'
3, 'c', 3, '2007-01-01 00:00:00'
3, 'c', 20, '2007-01-02 00:00:00'
4, 'p', 15, '2007-01-09 00:00:00'
4, 'p', 16, '2007-01-11 00:00:00'

Thanks!Start Free Trial