Mysql – Dynamic query in Stored Procedure over table values

MySQLstored-procedures

CREATE TABLE `Slot` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `starttime` datetime NOT NULL,
  `endtime` datetime NOT NULL,
  `shift_Id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

I have a stored procedure where I have a Temporary Table like :

TempSlot

| starttime              | endtime             |
| 2018-10-25 15:00:00    | 2018-10-25 16:00:00 |
| 2018-10-25 13:00:00    | 2018-10-25 14:00:00 |
| 2018-10-26 12:00:00    | 2018-10-26 13:00:00 |

Now, I need to check if any entry from TempSlot is present in the Slot table such that inserting it will create a conflict.

Am not sure how to write this query in stored procedure :

I want to write something like this :

SELECT COUNT(*)  FROM Slot
WHERE 
(((starttime >= '2018-10-25 15:00:00' and starttime < '2018-10-25 16:00:00') 
or
(endtime > '2018-10-25 15:00:00' and endtime <= '2018-10-25 16:00:00'))
OR
((starttime >= '2018-10-25 13:00:00' and starttime < '2018-10-25 14:00:00') 
or
(endtime > '2018-10-25 13:00:00' and endtime <= '2018-10-25 14:00:00'))
OR((starttime >= '2018-10-26 12:00:00' and starttime < '2018-10-26 13:00:00') 
or
(endtime > '2018-10-26 12:00:00' and endtime <= '2018-10-26 13:00:00'))
)

But I want a generic query. What will be a good way to write this ?

Best Answer

SELECT COUNT(*)
FROM Slots s, TempSlots t
WHERE (s.starttime < t.endtime AND t.starttime < s.endtime)
   OR (s.starttime > t.endtime AND t.starttime > s.endtime)

This query counts the overlapping pairs (one record from Slots overlapped one record from TempSlots) only.

To increase the performance you may try to divide this query by two UNIONed queries escaping OR.

Related Question