1
 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.

7
  • As I understand you want to find records which are out of the slot which they are assigned to (for example, slot 2634 now occupies 16:00-16:30, and appointment 132, which occupies 16:40-17:10 and is assigned to this slot, do not overlaps with it). Am I right? PS. In original state (the slotId 2634 which initially lets say had 1 hour totalTime) this slot overlaps with appointment 132 assigned to it only partially. Was it correct?
    – Akina
    Nov 2, 2018 at 7:29
  • Akina : the Apt's are only within a slot - they do not have a fix time. Meaning if slotId has one hour then customers can book Apt till there is freetime. We don't have rigid boundaries like 16:00-16:25 and 16:25-16.50. Its just 2 appointments of 25 mins - no dedicated time. And appointment 132, which occupies 16:40-17:10 and is assigned to this slot --> No interpretation is incorrect. created_At which 16:40 is only when the record was inserted. As I mentioned between 1 hour the appointments are relative they don't have absolute time.
    – j10
    Nov 2, 2018 at 7:46
  • So initially 2634 has 1 hour of total time and was able to reserve apt with Id 129 (booked first) and 132 (booked latest) but now the Slot has been modified to have only 30 mins of Total Time --> so I need to delete/reassign few appointments from 2634, since apt ID : 132 was booked latest it needs to be removed, 129 requires 25 mins and is < 30 mins of Total Time so it stays. a Slot can have any number of appointments. My example shows 2 appointments. Only apts which were booked asc & can be summed to the new total time shall remain. I want the apt_Id of the ones that have to be deleted.
    – j10
    Nov 2, 2018 at 7:48
  • If so simlpy select (or delete) appointments by appointment.slotid = slots.slotid and appointment.created_At not between slots.slotstarttime and slots.slotendtime.
    – Akina
    Nov 2, 2018 at 7:54
  • No. appointment.created_At has nothing to do with slots.slotstarttime and slots.slotendtime. I need to find out appointments which will not fit in the new totalTime and delete those
    – j10
    Nov 2, 2018 at 8:52

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.