Schedule - find nearest free spot

[RasPill]

Hello,
I've created a database for my small business(selling & changing tires) that holds data on clients, their cars & tires.
I've also created a schedule to sign people up for visits.
I've used table with:
'date',
'hour' (IDhour, a foreign key from a table that stores start and end time every 30 minutes, I have slots 0700-0730, 0730-0800, 0800-0830 and so on till 2130-2200)
client (IDclient a FK from the table that stores clients data).

I'm looking for a way to find the nearest free slot based on the visit time given by the client (visit can be 30, 60, 90 or 120 minutes).
I've tried different solutions but can't seem to find one that actually works .
Any ideas how to tackle that??

[UnklDonald418]

Based on my understanding of your database you could try a query something like

Code: Select all

SELECT "TimeSlots"."IDhour", "TimeSlots"."Hour", "S1"."IDclient", "S1"."date" 
FROM {OJ "TimeSlots" LEFT OUTER JOIN ( SELECT * FROM "Schedule" WHERE "Schedule"."date" = :Enter_Date) AS "S1" ON "TimeSlots"."IDhour" = "S1"."IDhour"} 
WHERE  "S1"."IDclient" IS NULL;

It displays a list of available time slots for the date entered at the parameter prompt.
I tried to match the column names you gave but there may be some differences.
I used a table “TimeSlots” with two columns "IDhour" and "Hour".
The table “schedule” has columns "ID", "date", "IDclient" and "IDhour".