根据条件选择班次 | 根据条件选择班次

本文介绍了根据条件选择班次的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

推荐答案

;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，您的预期输出是错误的。有五个时间段，因此员工1,2和3将有五个可用的时隙，而不是四个，emp4将有五个。

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

这篇关于根据条件选择班次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

1403页，肝出来的..