Get free time slots of an employee during scheduling in sql

Question

I have a set of employees and a set of time slots. For example

Employees

EMP1
EMP2
EMP3
EMP4
EMP5  

Time Slots

08:00AM-10:00AM
10:00AM-12:00PM
04:00PM-06:00PM
08:00AM-12:00PM
02:00PM-06:00PM
08:00AM-04:00PM
09:00AM-01:00PM
08:30AM-01:30PM
12:00PM-04:00PM
 2:00PM-3:00PM

An employee can be allocated to any timeslots. What I need is that, if an Employee is allocated in time slots 8:00Am-12:00PM then that Employee should not be available in 08:00AM-10:00AM and 10:00AM-12:00PM slots. Similarly, if an employee is allocated in 04:00PM-06:00PM then that employee should not be available in any of the timeslots between 04:00PM-06:00PM, but can be available before and after time slots, not in between.

How to do this?

Do you need help with the DB schema? Or with the query that gets the data? — Abdullah Dibas, Jul 23 '18 at 05:03
Please post the actual database type (SQL Server? Oracle) and also post what you've tried so far. — Nick.Mc, Jul 23 '18 at 05:08
You have a _terrible_ question history. Unclosed questions. Duplicate questions. It's quite obvious that you will either not return to this question or will simply not understand that answer — Nick.Mc, Jul 23 '18 at 05:19
Which [DBMS product](https://en.wikipedia.org/wiki/DBMS) are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... — , Jul 23 '18 at 05:49
If you know that actual version of SQL Server, please edit your question and add the appropriate tag. — Nick.Mc, Jul 23 '18 at 06:17
If all you want is a query, we need to know how the data is stored. How are the employees and timeslots linked? What does the table(s) look like? Can you have more than one employee in a given timeslot? — DancingFool, Jul 23 '18 at 06:30
In the first place how does an employee gets allocated a time slot ? Any criteria ? rule ? logic ? constraints ? — Squirrel, Jul 23 '18 at 06:45
@Squirrel Employees can be allocated in any time slot at first .I want to show a list of non allocated time slots in a grid with stored procedure.For example ,I have allocated EMP1 in timeslot (08:00AM-12:00PM) then agian in the non allocated list ,this employee should not be available in time slots between (08:00AM-12:00PM) and EMP 1 should be available only in timeslots 04:00PM-06:00PM,02:00PM-06:00PM,12:00PM-04:00PM 2:00PM-3:00PM. — Ann Sara, Jul 23 '18 at 08:06
can you please post your table DDL with sample data with exepcted result ? — Squirrel, Jul 23 '18 at 08:10
Possible duplicate of [Select shift times based on a condition](https://stackoverflow.com/questions/51496252/select-shift-times-based-on-a-condition) — Liam, Jul 24 '18 at 10:34

score -1 · Answer 1 · answered Jul 23 '18 at 06:54

I assume you have a bridge table that maps Employees and timeslots. I could develop a solution by the below schema i developed

SELECT * INTO dbo.Employees FROM (
SELECT 1 empid,'EMP1' emp
union
SELECT 2,'EMP2'
UNION
SELECT 3,'EMP3'
UNION
SELECT 4,'EMP4'
UNION
SELECT 5,'EMP5' )a

SELECT * INTO dbo.TimeSlots FROM (
SELECT 1 sltid,'08:00AM-10:00AM' slt
UNION
SELECT 2,'10:00AM-12:00PM'
UNION
SELECT 3,'04:00PM-06:00PM'
UNION
SELECT 4,'08:00AM-12:00PM'
UNION
SELECT 5,'02:00PM-06:00PM'
UNION
SELECT 6,'08:00AM-04:00PM'
UNION
SELECT 7,'09:00AM-01:00PM'
UNION
SELECT 8,'08:30AM-01:30PM'
UNION
SELECT 9,'12:00PM-04:00PM'
UNION
SELECT 10,'2:00PM-3:00PM')a


SELECT * INTO dbo.TimeSlotsemp FROM (
SELECT 1 emp,2 slt
UNION
SELECT 1,3
UNION 
SELECT 2,6)a

--available slots
SELECT DISTINCT a.empid,a.emp,d.slt AS availableslots FROM employees a
cross JOIN timeslots d
LEFT JOIN TimeSlotsemp b ON a.empid = b.emp
left JOIN timeslots c
ON b.slt = c.sltid
WHERE (d.sltid NOT IN (SELECT slt FROM timeslotsemp WHERE emp = a.empid) or a.empid NOT IN (SELECT emp FROM timeslotsemp))

Get free time slots of an employee during scheduling in sql

1 Answers1