CODEWITHSUNDEEP
c#sqlasp.netsql-server
Ann Sara
Ann Sara

Reputation: 69

Select shift times based on a condition

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

Upvotes: 0

Views: 288

Answers (3)

KumarHarsh
KumarHarsh

Reputation: 5094

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.

Upvotes: 0

Cato
Cato

Reputation: 3701

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270493

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;

Upvotes: 2

Related Questions