Select shift times based on a condition

Question

I have three tables such as employees,Time slots and Scheduling . For example

Time Slots table : 
 id     time
  1     08:00:00    10:00:00
  2     10:00:00    12:00:00
  3     16:00:00    18:00:00
  4     08:00:00    16:00:00 
  5     14:00:00    18:00:00



Employee Table:

 EMP1
 EMP2
 EMP3
 EMP4


Scheduling table :

 EMP    TIMESLOTS ID 
 EMP1     1
 EMP2     2
 EMP3     4

I need to show the list of free employees in the time slots in a Gridview, ie since EMP3 is allocated in ( 08:00:00 16:00:00) ,then it should not be shown in any time slots between 08:00:00 16:00:00 ,but can be shown in any timeslot before 08:00:00 and after 16:00:00 ,similarly for all the scheduled Employees . If any of the employees is not scheduled on any time slots ,then that employee should be available in every time slots . ie ,EMP5 should be available in all time slots .

My output should be like this  :

   EMPLOYEES          FREE TIME SLOTS
    EMP1                   10:00:00    12:00:00
    EMP1                   14:00:00    18:00:00
    EMP1                   16:00:00    18:00:00
    EMP2                   08:00:00    10:00:00
    EMP2                   16:00:00    18:00:00
    EMP2                   14:00:00    18:00:00
    EMP3                   16:00:00    18:00:00
    EMP4                   08:00:00    10:00:00
    EMP4                   10:00:00    12:00:00
    EMP4                   16:00:00    18:00:00
    EMP4                   08:00:00    16:00:00 
    EMP4                   14:00:00    18:00:00

Any help will really appreaciated.Thanks in advance

It would be great if you could share a [mcve] with your progress so far. — mjwills, Jul 24 '18 at 10:28
You can't expected the complete solution... (this it's your part). [Question Repeated](https://stackoverflow.com/questions/17407481/check-if-a-time-is-between-two-times-time-datatype) — R. García, Jul 24 '18 at 10:32
I think you are missing the logic for whether or not two shifts overlap. for shift A and B, to avoid overlap, A has to either end before B starts, or A starts after B ends - so no overlap is A.end <= B.start or A.start >= B.end. If you take the 'NOT' of that it comes up as A.end > B.Start And A.start < B.end would detect an overlap between A and B — Cato, Jul 24 '18 at 11:09

score 2 · Answer 1 · answered Jul 24 '18 at 10:30

2

Use a cross join to generate all the combinations of employees and timeslots. Then use a left join (or not in or not exists) to filter out the ones that exist:

select e.emp, ts.*
from employee e cross join
     timeslots ts left join
     scheduling s
     on s.emp = e.emp and s.timeslot_id = ts.timeslot_id
where s.emp is null;

answered Jul 24 '18 at 10:30

Gordon Linoff

1,242,037
58
646
786

Thank you for your answer .How to filter out the existing one ie I need to filter out the existing ones ,since I am stuck here .I need to filter out everything that comes in between the scheduled time slots . – Ann Sara Jul 24 '18 at 10:40

score 0 · Answer 2 · answered Jul 24 '18 at 10:57

0

SELECT e.*, t.* 
            FROM employee e 
            CROSS JOIN 
            TimeSlot t
                WHERE NOT EXISTs (
                                    SELECT 0 FROM Scheduling s2 
                                                JOIN TimeSlot t2 
                                                        ON s2.empid = e.empid 
                                                            AND t2.endTime > t.StartTime 
                                                            AND t2.startTime < t.EndTime 

                                )  --where there is not some other overlapping timeslot allocated

answered Jul 24 '18 at 10:57

Cato

3,652
9
12

Thank you for your answer . Still I am stuck in filtering out the timeslots . How to filter out the time slots againt an employee. – Ann Sara Jul 24 '18 at 11:21
so for an employee, you know how to find the timeslots he could have, but you want to know how to check if they overlap with any other timeslot already allocated to him, and then filter those out? Is that what you are asking how to do? – Cato Jul 24 '18 at 11:35
If you make minimal tables, with some minimal data, we would probably be able to guide you on making SQL. however we don't have that, so we've been guessing to some extent to give guidance. – Cato Jul 24 '18 at 11:37
Yes . I need to know how to filter out those existing employees overlap time slots. – Ann Sara Jul 24 '18 at 11:38
so going back to the original answers with cross joins, if you cross join employees and slots, you get all combinations of employees and timeslots - except a timeslot that overlaps might already been allocated to that employee, so you can then put a query in a NOT EXISTS that detects if there is such an overlap, and then exclude that selection – Cato Jul 24 '18 at 12:08
Thank you .But using the above solution you provided , it will filter out all time slots of an employee if it is allocated in sceduling . However ,I need to get the time slots for that that does not overlap. – Ann Sara Jul 24 '18 at 12:21
The intention there was that it would only filter out a particular time slot for an employee if there is another slot allocated to the employee, overlapping with the slot in question – Cato Jul 24 '18 at 13:22

KumarHarsh · Answer 3 · 2018-07-30T11:57:35.903

Try this,

Declare @TimeSlots table (id int,stime time,etime time)
insert into @TimeSlots VALUES
 (1 ,'08:00:00','10:00:00')
,(2 ,'10:00:00','12:00:00')
,(3 ,'16:00:00','18:00:00')
,(4 ,'08:00:00','16:00:00')
,(5 ,'14:00:00','18:00:00')

Declare @Employee Table (Emp varchar(40))
insert into @Employee VALUES
('EMP1'),('EMP2')
,('EMP3'),('EMP4')

Declare @Scheduling table (EMP varchar(40), TIMESLOTSID int)
insert into @Scheduling VALUES
('EMP1',1),('EMP2',2),('EMP3',4)),('EMP3',3)


    ;WITH CTE
AS (
    SELECT e.Emp
        ,s.TIMESLOTSID
        ,ts.stime
        ,ts.etime
    FROM @Employee E
    LEFT JOIN @Scheduling S ON e.Emp = s.EMP
    LEFT JOIN @TimeSlots TS ON s.TIMESLOTSID = ts.id
    )
,CTE1 AS(
    SELECT c.emp
        ,c.stime
        ,c.etime
        ,oa.*
        ,CASE 
            WHEN oa.FreeStartTime >= c.stime
                AND FreeStartTime < c.etime
                THEN 0
            ELSE CASE 
                    WHEN stime >= FreeStartTime
                        AND stime < FreeEndTime
                        THEN 0
                    ELSE 1
                    END
            END RequireSlot
    FROM cte c
    OUTER APPLY (
        SELECT TS.stime FreeStartTime
            ,ts.etime FreeEndTime
        FROM @TimeSlots TS
        ) oa
)

SELECT c.emp
    ,c.FreeStartTime
    ,c.FreeEndTime
FROM CTE1 c
WHERE RequireSlot = 1
    AND NOT EXISTS (
        SELECT 1
        FROM cte1 c1
        WHERE c.emp = c1.emp
            AND c.FreeStartTime = c1.FreeStartTime
            AND c.FreeEndTime = c1.FreeEndTime
            AND c1.RequireSlot = 0
        )

If it do not work with other sample data then please throw that sample.

Alternatively if you have lot of such data to be shown then it can be handle at application level very easily.

you only need 2 small resultset in application.

Select * from CTE

select * from @TimeSlots

Rest logic in application code.

Thank you.But It wont work if we add ('EMP3',3) in @Scheduling table. — Ann Sara, Jul 30 '18 at 08:39
If we add two entries for same employee in scheduling table ,it wont work. — Ann Sara, Jul 30 '18 at 08:42
@AnnSara , you can check my edited script.If any issue then let me know.See while inserting record in Scheduling you can insert id 3 for Emp3 but you can't insert other time slot which will cause overlap. I will check it again t'row — KumarHarsh, Jul 30 '18 at 12:01
I'm sorry ,was little busy with other things .I will check it , and update you .Thank you very much for your support since I was stuck in this for days . — Ann Sara, Aug 01 '18 at 09:28

Select shift times based on a condition

3 Answers3

Linked