🚀 go-pugleaf

Hi,

I have huge problems figuring out how to write my stored procedure, 
which will return all times in a day (SELDAY) a staff (ID_STAFF) is free 
after a selected time (START_TIME). Free is the same as not having 
anything booked in my application. STIME is the length of the selected 
treatment.

I want it too return something like this:

32
33
36
37
38
42
...

The time is numbers of quarters, so 32 is the same as 32 / 4 = 8:00.

The problem is when a staff has something booked, lets say for time 32
and 33. I will then get this error: multiple rows in singleton select.

The select statement in my while loop (select BD.ID_BOOKING) will return 
the same ID_BOOKING both when I is 32 as when I is 33.

This is the stored procedure:

ALTER PROCEDURE "WEB_FREETIMES"
(
   "ID_SERVICE" INTEGER,
   "SELDAY" DATE,
   "START_TIME" INTEGER,
   "ID_STAFF" INTEGER
)
RETURNS
(
   "FREE_TIME" INTEGER
)
AS
DECLARE VARIABLE "SERVICE_TIME" INTEGER;
DECLARE VARIABLE "I" INTEGER;
DECLARE VARIABLE "NCOUNT" INTEGER;
DECLARE VARIABLE "OPENING_END" INTEGER;
DECLARE VARIABLE "SHIFT_END" INTEGER;
BEGIN
   select STIME
   from SERVICE
   where ID_SERVICE = :ID_SERVICE
   into :SERVICE_TIME;

   select SHIFT_END
   from SHIFT
   where ID_STAFF = :ID_STAFF
   and SHIFT_DATE = :SELDAY
   into :SHIFT_END;

   if (SHIFT_END is null) then
     SHIFT_END = 0;

   I = START_TIME;
   WHILE (I <= SHIFT_END) DO
   BEGIN
     IDBOOKING = 0;

     select BD.ID_BOOKING
     from BOOKING_DETAIL BD
     left join BOOKING B on (BD.ID_BOOKING = B.ID_BOOKING)
     where BD.ID_STAFF = :ID_STAFF
     and B.BOOKING_DATE = :SELDAY
     and (BD.TIME_BEGIN between :I and :SERVICE_TIME + :I - 1
       or BD.TIME_BEGIN + BD.TIME_LENGTH - 1 between :I and 
:SERVICE_TIME + :I - 1)
     INTO :IDBOOKING;

     IF (!IDBOOKING > 0) THEN
     BEGIN
       FREE_TIME = :I;
       SUSPEND;
     END
     I = I + 1;
   END
END;

Anyone can help me?

// Johan