How to do this selection?

Thread View: borland.public.interbase.sql

2 messages

[T] Back to Threads [V] Tree View [G] Group Articles

2 total messages Started by Johan Anderlund Sat, 12 Jul 2008 17:33

#180

Author: Johan Anderlund
Date: Sat, 12 Jul 2008 17:33

87 lines
2821 bytes

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

Re: How to do this selection?

#181

Author: Karol Bieniaszewski
Date: Mon, 14 Jul 2008 05:32

93 lines
3271 bytes

Uzytkownik "Johan Anderlund" <johan@anderlund.net> napisal w wiadomosci 
news:4878eaf0$1@newsgroups.borland.com...
> 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

use Distinct keyword in select - to return only one unique row

Karol Bieniaszewski

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads