CODEWITHSUNDEEP
sqlsql-serverleft-joininner-join
abramlimpin
abramlimpin

Reputation: 5077

Display all available users on a given day

I want to display all available users (user type: employee) on a given schedule date. They are not available if they are scheduled both day (PM/AM)

Here are my following tables:

User Types

TypeID     TypeName
1          Admin
2          Employee

Users

UserID     TypeID     Name
1          1          Admin 1
2          2          Employee 1
3          2          Employee 2
4          1          Admin 2
5          2          Employee 3
6          2          Employee 4
7          2          Employee 5

Schedule

SchedID   UserID      SchedDate     Day (PM/AM)
1         2           8/27/2013     PM
2         2           8/27/2013     AM
3         3           8/27/2013     AM
4         5           8/27/2013     PM
5         6           8/27/2013     AM

Expected Result (WHERE SchedDate='8/27/2013')

UserID    Name
3         Employee 2
5         Employee 3
6         Employee 4
7         Employee 5

This is my current SQL statement:

SELECT Users.UserID, Users.Name FROM Users LEFT OUTER JOIN 
Schedule ON Schedule.UserID = Users.UserID WHERE Users.TypeID = 5

Upvotes: 1

Views: 79

Answers (2)

Mikkey
Mikkey

Reputation: 11

SELECT USERS.USERID, 
       USERS.NAME 
FROM   USERS 
WHERE  NOT EXISTS (SELECT SCHEDID 
                   FROM   SCHEDULE 
                   WHERE  SCHEDULE.USERID = USERS.USERID 
                          AND DAY = 'AM') 
       AND NOT EXISTS (SELECT SCHEDID 
                       FROM   SCHEDULE 
                       WHERE  SCHEDULE.USERID = USERS.USERID 
                              AND DAY = 'PM')

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Let's phrase this a little differently. A user is unavailable if the user has both AM and PM scheduled for the DAY column. Otherwise, the user is available.

Given that there are only two values in that column, the following query does the filtering you want:

SELECT u.UserID, u.Name
FROM Users u LEFT OUTER JOIN 
     Schedule s
     ON s.UserID = u.UserID and
        s.ScheduleDate = '2013-08-27'
WHERE u.TypeID = 5
GROUP BY u.UserID, u.Name
HAVING COUNT(distinct s.day) < 2;

If you know the values are never repeated, then you can change the having clause to:

HAVING COUNT(*) < 2;

This is a bit of a trick. When there is no match in the schedule table at all, the counts will return 0 (in the first case) or 1 (in the second case).

Upvotes: 3

Related Questions