Schedule - find nearest free spot

Creating and using forms
Post Reply
RasPill
Posts: 1
Joined: Sun Apr 22, 2018 3:40 pm

Schedule - find nearest free spot

Post by 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 :knock: :crazy: :oops: .
Any ideas how to tackle that??
RasPill
Windows 10/OpenOffice (Apache) 4.1
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Schedule - find nearest free spot

Post by 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".
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Post Reply