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??
Schedule - find nearest free spot
Schedule - find nearest free spot
RasPill
Windows 10/OpenOffice (Apache) 4.1
Windows 10/OpenOffice (Apache) 4.1
-
- Volunteer
- Posts: 1546
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Schedule - find nearest free spot
Based on my understanding of your database you could try a query something like
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".
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;
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
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11