Schedule - find nearest free spot

Creating and using forms

Schedule - find nearest free spot

Postby RasPill » Sun Apr 22, 2018 4:34 pm

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
RasPill
 
Posts: 1
Joined: Sun Apr 22, 2018 3:40 pm

Re: Schedule - find nearest free spot

Postby UnklDonald418 » Mon Apr 23, 2018 4:38 am

Based on my understanding of your database you could try a query something like
Code: Select all   Expand viewCollapse view
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.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1179
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA


Return to Forms

Who is online

Users browsing this forum: No registered users and 2 guests