根据条件选择班次

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

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