Select shift times based on a condition

Question

0.00/5 (No votes)

See more:

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 .

What I have tried:

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

Posted 24-Jul-18 0:28am

Member 12926744

Updated 24-Jul-18 1:36am

Add a Solution

Comments

Santosh kumar Pithani 24-Jul-18 6:48am

we are not ready to do your home work;try to put query in 'what i have tried' :-)

Member 12926744 24-Jul-18 6:52am

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;

This is what I have tried .I am stuck in filtering out the time slots .

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

#realJSOP · Answer 1 · 2018-07-24T01:37:00

SQL

;WITH cte AS
(
    SELECT    a.name,
              CASE WHEN c.slotid IS null THEN 0 ELSE c.slotid END AS slotid
    FROM      #employees AS a 
    LEFT JOIN #schedules AS c ON a.name LIKE c.empname
)
SELECT     a.name,
           b.timestart,
           b.timeend
FROM       cte AS a 
INNER JOIN #timeslots AS b ON a.slotid <> b.id

BTW, your expected output is wrong. There are five time slots, so employees 1, 2, and 3 will have FIVE available timeslots, not four, and emp4 will have five.

name	slotid	timestart		timeend
emp1	2	10:00:00.0000000	12:00:00.0000000
emp1	3	16:00:00.0000000	18:00:00.0000000
emp1	4	08:00:00.0000000	16:00:00.0000000
emp1	5	14:00:00.0000000	18:00:00.0000000
emp2	1	08:00:00.0000000	10:00:00.0000000
emp2	3	16:00:00.0000000	18:00:00.0000000
emp2	4	08:00:00.0000000	16:00:00.0000000
emp2	5	14:00:00.0000000	18:00:00.0000000
emp3	1	08:00:00.0000000	10:00:00.0000000
emp3	2	10:00:00.0000000	12:00:00.0000000
emp3	3	16:00:00.0000000	18:00:00.0000000
emp3	5	14:00:00.0000000	18:00:00.0000000
emp4	1	08:00:00.0000000	10:00:00.0000000
emp4	2	10:00:00.0000000	12:00:00.0000000
emp4	3	16:00:00.0000000	18:00:00.0000000
emp4	4	08:00:00.0000000	16:00:00.0000000
emp4	5	14:00:00.0000000	18:00:00.0000000