Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I have done something similar to what your trying to do so I understand how complicated it is :)</p> <p>This was done in MSSQL so you will need to convert it to MySql.</p> <p><img src="https://i.stack.imgur.com/2XOw0.gif" alt="example of appointments"></p> <p><strong>These are the tables we ended up with:</strong></p> <p><strong><em>TimeSlots:</em></strong></p> <p>We store both the default timesslots &amp; modified timeslots for each staff member in this table (We have a column called "SlotType" in this table. SlotType 1 = DEFAULT TIMESLOTS &amp; SlotType 2 = MODIFIED TIMESLOTS). If you look at "Tue 30/04/13" in the picture above you will see that we modified the timeslots for that day to only display a 9am appointment for this particular staff member.</p> <p><strong><em>ClosedDays:</em></strong></p> <p>This is a list of closed days - for example a staff member my not work on his birthday &amp; Christmas day.</p> <p><strong><em>Appointments:</em></strong></p> <p>This is a list of appointments that have been booked (or waiting for booking confirmation).</p> <p><strong>SQL Query to get available appointments:</strong></p> <p>To check to appointments we then used the following SQL in our stored procedure. It checks one staff members appointments for the date specified. The final stored procedure that we are using loops though each staff member on the page for each day of the week to get all the appointments. Using this query to get 10 staff members appointments for the next 7 days = a total of 70 query's &amp; takes about 300ms with a million records in each table. We are loading the appointments via ajax so 300ms is acceptable for our use &amp; will prob change it to get each staff members appointments separately via ajax (so 7 query's at a time) to improve performance even more in the future.</p> <pre><code>DECLARE @MyDate date, @MyDayName nvarchar(10); IF @StartDate IS NULL SET @StartDate = GETDATE(); SET @MyDate = CAST(@StartDate AS date); SET @MyDayName = DATENAME(dw, @MyDate ); --NOTES: --@SlotType = 1 (DEFAULT TIMESLOTS), 2 (MODIFIED TIMESLOTS) --***CHECK TO SEE IF DOCTOR IS CLOSED TODAY*** IF NOT EXISTS (SELECT [ClosedDays].[ID] FROM [ClosedDays] WHERE [ClosedDays].[StaffID] = @StaffID AND [ClosedDays].[BusinessID] = @BusinessID AND [ClosedDays].[Active] = 1 AND @MyDate BETWEEN [ClosedDays].[StartDate] AND [ClosedDays].[EndDate]) BEGIN --***THE DOCTOR IS NOT CLOSED TODAY SO GET THE AVAILABLE TIMESLOTS*** --***CHECK TO SEE IF DOCTOR IS HAS MODIED TIMESLOTS TODAY*** IF NOT EXISTS (SELECT [TimeSlots].[ID], @MyDate AS SlotDate FROM [TimeSlots] WHERE [TimeSlots].[StaffID] = @StaffID AND [TimeSlots].[BusinessID] = @BusinessID AND [TimeSlots].[Active] = 1 AND [TimeSlots].[SlotType] = 2 AND [TimeSlots].[SlotDay] = @MyDayName AND @MyDate BETWEEN [TimeSlots].[StartDate] AND [TimeSlots].[EndDate] AND [TimeSlots].[ID] NOT IN (SELECT [Appointments].[TimeSlotID] FROM [Appointments]) ) BEGIN --***THE DOCTOR HAS NO MODIFIED TIMESLOTS FOR TODAY USE THE DEFAULT ONES*** SELECT [TimeSlots].[ID] AS SlotID, [TimeSlots].[StaffID], [TimeSlots].[BusinessID], CONVERT(nvarchar(10), @MyDate, 103) AS SlotDate, [TimeSlots].[SlotDay], LTRIM(RIGHT(CONVERT(nvarchar(10), [TimeSlots].[SlotTime], 100), 7))AS SlotTime FROM [TimeSlots] WHERE [TimeSlots].[StaffID] = @StaffID AND [TimeSlots].[BusinessID] = @BusinessID AND [TimeSlots].[Active] = 1 AND [TimeSlots].[SlotType] = 1 AND [TimeSlots].[SlotDay] = @MyDayName AND @MyDate BETWEEN [TimeSlots].[StartDate] AND [TimeSlots].[EndDate] AND NOT EXISTS (SELECT [Appointments].[TimeSlotID] FROM [Appointments] WHERE [Appointments].[TimeSlotID] = [TimeSlots].[ID]) END ELSE BEGIN --***THE DOCTOR HAS MODIFIED TODAYS TIMESLOTS SO USE THE MODIFIED TIMESLOTS*** SELECT [TimeSlots].[ID] AS SlotID, [TimeSlots].[StaffID], [TimeSlots].[BusinessID], CONVERT(nvarchar(10), @MyDate, 103) AS SlotDate, [TimeSlots].[SlotDay], LTRIM(RIGHT(CONVERT(nvarchar(10), [TimeSlots].[SlotTime], 100), 7))AS SlotTime FROM [TimeSlots] WHERE [TimeSlots].[StaffID] = @StaffID AND [TimeSlots].[BusinessID] = @BusinessID AND [TimeSlots].[Active] = 1 AND [TimeSlots].[SlotType] = 2 AND [TimeSlots].[SlotDay] = @MyDayName AND @MyDate BETWEEN [TimeSlots].[StartDate] AND [TimeSlots].[EndDate] AND NOT EXISTS (SELECT [Appointments].[TimeSlotID] FROM [Appointments] WHERE [Appointments].[TimeSlotID] = [TimeSlots].[ID]) END END ELSE BEGIN --***NO APPOINTMENTS WERE FOUND*** --***DUMMY QUERY TO RETURN NO RECORDS*** SELECT [TimeSlots].[ID] AS SlotID, [TimeSlots].[StaffID], [TimeSlots].[BusinessID], CONVERT(nvarchar(10), @MyDate, 103) AS SlotDate, [TimeSlots].[SlotDay], LTRIM(RIGHT(CONVERT(nvarchar(10), [TimeSlots].[SlotTime], 100), 7))AS SlotTime FROM [TimeSlots] WHERE [TimeSlots].[ID] = -0 END </code></pre> <p>Hope this makes sense &amp; if anyone else has some idea's on how to optimize this more please let me know!</p>
    singulars
    1. This table or related slice is empty.
    1. POHow to structure and query an appointment system based on time slots where each bookable entity has a different time table daily?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USMWD
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow to structure and query an appointment system based on time slots where each bookable entity has a different time table daily?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload