Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Example table and data set for finding groupings of rows based on time overlap
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
# Dump of table example
# ------------------------------------------------------------
DROP TABLE IF EXISTS `example`;
CREATE TABLE `example` (
`id` int(10) unsigned NOT NULL DEFAULT '0',
`borrower_id` int(10) unsigned NOT NULL,
`started` timestamp NULL DEFAULT NULL,
`closed` timestamp NULL DEFAULT NULL,
`dead` timestamp NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `example` WRITE;
/*!40000 ALTER TABLE `example` DISABLE KEYS */;
INSERT INTO `example` (`id`, `borrower_id`, `started`, `closed`, `dead`)
VALUES
(7714,238846,'2015-01-27 15:14:50','2015-02-02 14:14:13',NULL),
(7882,238846,'2015-01-28 13:25:58',NULL,'2015-05-15 12:16:07'),
(13190,259140,'2015-03-17 10:11:44',NULL,'2015-03-18 07:31:57'),
(13192,259140,'2015-03-17 10:12:17',NULL,'2015-03-18 11:46:46'),
(13194,259140,'2015-03-17 10:12:53',NULL,'2015-03-18 11:46:36'),
(14020,259140,'2015-03-23 14:32:16','2015-03-24 15:57:32',NULL),
(17124,242650,'2015-04-16 16:19:08','2015-04-16 16:21:06',NULL),
(19690,238846,'2015-05-15 13:17:31',NULL,'2015-05-27 13:56:43'),
(20038,242650,'2015-05-19 15:38:17',NULL,NULL),
(20040,242650,'2015-05-19 15:39:58',NULL,'2015-05-21 12:01:02'),
(20302,242650,'2015-05-21 13:09:06',NULL,NULL),
(20304,242650,'2015-05-21 13:09:54',NULL,NULL),
(20306,242650,'2015-05-21 13:10:19',NULL,NULL),
(20308,242650,'2015-05-21 13:12:20',NULL,NULL),
(21202,238846,'2015-05-29 16:47:29',NULL,NULL),
(21204,238846,'2015-05-29 16:47:56',NULL,NULL),
(21208,238846,'2015-05-29 17:05:15',NULL,NULL),
(21210,238846,'2015-05-29 17:05:55',NULL,NULL),
(21918,242650,'2015-06-04 17:04:29',NULL,'2015-06-12 15:47:23');
/*!40000 ALTER TABLE `example` ENABLE KEYS */;
UNLOCK TABLES;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.