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.
appointment.slotid = slots.slotid and appointment.created_At not between slots.slotstarttime and slots.slotendtime
.