Query from multiple "Not in" queries without using UNION
It's hard to make a good title for what I need.
This revolves around 3 tables and is an appointment booking system and I need to get a list of every doctor / nurse and time slots they are free.
I don't want to use UNION in the same way as the first sql below, this means I have to declare the names of each doctor, which I don't want to do.
Table...
UserDetails:
ID Role Surname Clinic
1 Doctor House 1
2 Doctor Bob 1
3 Nurse Smith 1
4 Doctor Jim 2
5 Nurse Grant 2
6 Patient Billy 1
7 Patient Jones 1
Timeslots:
ID TimeSlot
1 10:00
2 10:30
3 11:00
4 11:30
11 16:30
12 17:00
Appointments:
ID StaffID PatientID TimeSlot AppDate
1 1 6 1 today
2 1 7 3 today
3 2 6 2 today
4 1 6 4 tomorrow
(StaffID and PatientID is the foreign key identifier from the users table) I need a query that outputs every time slot and Doc / Nurse when they have no appointment to date (or any day based on "AppDate").
I can do this for a specific doctor / nurse:
SELECT TimeSlots.TimeSlot, Users.Role, Users.Surname, Users.Clinic
FROM TimeSlots, Users
WHERE
TimeSlots.ID NOT IN
(SELECT Appointments.TimeSlot
FROM Appointments
INNER JOIN Users
ON Appointments.MedicalStaffID = Users.ID
WHERE AppDate = CONVERT(DATE,GETDATE()) AND Users.Surname = 'House')
AND
Users.Surname = 'House'
ORDER BY TimeSlots.TimeSlot;
Which gives me:
TimeSlot Role Surname Clinic
10:30 Doctor House 1
11:30 Doctor House 1
16:30 Doctor House 1
17:00 Doctor House 1
This is fine, but I need 1 query to display this for all doctors / nurses, so I have:
TimeSlot Role Surname Clinic
10:30 Doctor House 1
11:30 Doctor House 1
16:30 Doctor House 1
17:00 Doctor House 1
10:00 Doctor Bob 1
11:00 Doctor Bob 1
11:30 Doctor Bob 1
16:30 Doctor Bob 1
17:00 Doctor Bob 1
and therefore it can also be ordered by time.
I first tried working with:
SELECT TimeSlots.TimeSlot, Users.Role, Users.Surname, Users.Clinic
FROM TimeSlots, Users
WHERE
TimeSlots.ID NOT IN (SELECT TimeSlot FROM Appointments WHERE AppDate = GETDATE() AND (Users.Clinic = 'Werrington') AND (Users.Role = 'Doctor' OR Users.Role = 'Nurse'))
AND
(Users.Role = 'Doctor' OR Users.Role = 'Nurse')
AND
(Users.Clinic = 'Werrington')
ORDER BY TimeSlots.TimeSlot;
But it just outputs every Doctor with every Timeslot
On a side note, I feel like the best way to structure the look of the resulting table is not being able to think about how.
source to share
If I understand the question correctly, this should give you what you want ...
SET NOCOUNT ON;
DECLARE @UserDetails TABLE (
ID int,
Role varchar(50),
Surname varchar(50),
Clinic int )
INSERT @UserDetails VALUES (1, 'Doctor', 'House', 1)
INSERT @UserDetails VALUES (2, 'Doctor', 'Bob', 1)
INSERT @UserDetails VALUES (3, 'Nurse', 'Smith', 1)
INSERT @UserDetails VALUES (4, 'Doctor', 'Jim', 2)
INSERT @UserDetails VALUES (5, 'Nurse', 'Grant', 2 )
INSERT @UserDetails VALUES (6, 'Patient', 'Billy', 1)
INSERT @UserDetails VALUES (7, 'Patient', 'Jones', 1)
DECLARE @TimeSlots TABLE (
ID int,
TimeSlot varchar(50) )
INSERT @TimeSlots VALUES (1 , '10:00' )
INSERT @TimeSlots VALUES (2 , '10:30' )
INSERT @TimeSlots VALUES (3 , '11:00' )
INSERT @TimeSlots VALUES (4 , '11:30' )
INSERT @TimeSlots VALUES (11, '16:30' )
INSERT @TimeSlots VALUES (12, '17:00' )
DECLARE @Appointments TABLE (
ID int,
StaffID int,
PatientID int,
TimeSlotID int,
AppDate varchar(50) )
INSERT @Appointments VALUES (1, 1, 6, 1, 'today' )
INSERT @Appointments VALUES (2, 1, 7, 3, 'today' )
INSERT @Appointments VALUES (3, 2, 6, 2, 'today' )
INSERT @Appointments VALUES (4, 1, 6, 4, 'tomorrow' )
SET NOCOUNT OFF;
WITH CompleteSchedule AS (
SELECT UD.ID as UserID, UD.Role, UD.Surname, UD.Clinic, TS.ID as TimeSlotID, TS.TimeSlot
FROM @UserDetails UD
CROSS JOIN @TimeSlots TS
)
SELECT CS.*
FROM CompleteSchedule CS
LEFT JOIN @Appointments A ON A.StaffID = CS.UserID AND A.TimeSlotID = CS.TimeSlotID AND A.AppDate = 'today'
WHERE A.ID is null
ORDER BY CS.UserID, CS.TimeSlotID
The CTE will generate a "table" for every employee X every time slot. Then you Left Join that with your assigned for the given day. Any resulting string that does not have an appointment ID is an open timeslot.
source to share