Select ID of available time slot for booking an appointment

Question

I have a table called Appointments displaying time slots for booking an appointment.

id | time start | time end | caseno
1  |  800       | 830      | null
2  |  800       | 830      | null
3  |  900       | 930      | AB-111
4  |  900       | 930      | null

I need to display available appointment slots where CaseNo is null. However, there are two time slots available each time (8am, 830am, 900am, etc.).

What query do I use to display the columns: Apn_Id, Apn_TimeStart and Apn_TimeEnd avoiding to display duplicates and only taking available time slots (caseno = null)?

Which dbms are you using? What's the expected result? Can you show us your current query? — jarlh, Jun 07 '15 at 09:15
For the data sample in your question what is the expected result that you are looking for?? — Mahmoud Gamal, Jun 07 '15 at 09:15

score 1 · Answer 1 · answered Jun 07 '15 at 09:27

You can do it this way:

SELECT MIN(id) as id,time_start,time_end
FROM Appointments 
GROUP BY time_start,time_end
HAVING SUM(CASE WHEN caseno IS NOT NULL THEN 1 ELSE 0 END) = 0

Explanation:

Having clause is for taking only the available time slots (case = null).

MIN is for GROUP BY (to avoid duplicates).

Result:

id  time_start  time_end
------------------------
1   800         830

Sample result in SQL Fiddle.

score 0 · Answer 2 · answered Jun 07 '15 at 12:11

If you want to find appointments that are NULL, a HAVING clause is the way to go. If you want all slots to be NULL, then use:

HAVING COUNT(caseno) = 0

If you want any slot to be 'NULL`, then use:

HAVING COUNT(caseno) < COUNT(*)

Remember: COUNT(<column name>) counts the number of non-NULL values in the column.

Select ID of available time slot for booking an appointment

2 Answers2