CREATE TEMPORARY TABLE TempSlotUpdated ( slotId INT(10) NULL, slotstarttime datetime NULL, slotendtime datetime NULL, totalTime time NULL); CREATE TABLE `Apt` ( `slot_Id` int(10) unsigned DEFAULT NULL, `apt_Id` int(10) unsigned DEFAULT NULL, `time` time NOT NULL, `assigned_To_Id` int(10) unsigned NOT NULL, `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, ) ENGINE=InnoDB DEFAULT CHARSET=latin1
Now, lets say if the Slots are updated so I need to delete some appointments e.g.
Apt Table :
| slot_Id | apt_Id | time | assigned_To_Id | created_At | 2634 | 129 | 00:25:00 | 2 | 2018-10-25 16:00:00 | 2634 | 132 | 00:30:00 | 2 | 2018-10-26 16:40:00 | 2635 | 156 | 00:20:00 | 2 | 2018-10-27 16:00:00 | 2635 | 160 | 00:25:00 | 2 | 2018-10-27 16:40:00
TempSlotUpdated Table has :
| slotId | slotstarttime | slotendtime | totalTime | 2634 | 2018-10-31 16:00:00 | 2018-10-31 16:30:00 | 00:30:00 | 2635 | 2018-10-30 16:00:00 | 2018-10-30 16:30:00 | 00:30:00
Now, the slotId 2634 which initially lets say had 1 hour totalTime now only has 30 mins totalTime, so I need to find out the latest appointments that have to be reassigned/deleted.
So referring to Apt Table,
it will be record with apt_Id = 132 for slot_Id = 2634 & record with apt_Id = 160 for slot_Id = 2635
I tried using Group By and Having but I am not sure how to use a condition for set of rows inside a Group.
This is what I Tried :
SET @suma = TIME('00:00:00'); Select * from (SELECT @suma:= ADDTIME(@suma, `time`) , CAST(@suma as Time) as time2, apt_Id FROM Apt WHERE slot_Id = 2634 ORDER BY Apt.created_At ASC) as abc Where abc.time2 >= TIME('00:30:00');
I tried with hard coded value and this works for one value of slot ie. 2634 and 00:30:00 totalTime I am now trying to use it with TempSlotUpdated table now which has multiple slots whose totalTime has changed.