选择基于员工的可用性查询SQL Server 2012

Question

我有两个表，一个用于员工，另一个表示当前和过去的活动。我需要一个函数/查询来根据类型为下一个活动选择一个员工。这里是样本数据：

表员工

EmployeesID UserName Type Available 
1   Bill Clerical 1 
2   Ann  Clerical 1 
3   John Technical 1 
4   Jack Technical 0 
5   Mary Clerical 0 
6   Sam  Technical 0 
7   Mark Clerical 1 
8   Andy Clerical 1 
9   Rick Clerical 1 
10   Nancy Clerical 1 

活动

ActivityID EmployeesID Status Task_Datetime 
101    2 Complete 6/11/2016 10:00 
102    1 Complete 6/11/2016 10:12 
103    3 Complete 6/11/2016 10:24 
104    4 Complete 6/11/2016 10:36 
105    7 Complete 6/11/2016 10:48 
106    6 Complete 6/11/2016 11:00 
107    5 Complete 6/11/2016 11:12 
108    8 Complete 6/11/2016 11:24 
109    10 Complete 6/11/2016 11:36 
110    9 Complete 6/11/2016 11:48 
111    5 Complete 6/11/2016 12:00 
112    8 Complete 6/11/2016 12:12 
113    3 Complete 6/11/2016 12:24 
114    4 Complete 6/11/2016 12:36 
115    1 Complete 6/11/2016 12:48 
116    6 Complete 6/11/2016 13:00 
117    7 Complete 6/11/2016 13:12 
118    2 Complete 6/11/2016 13:24 
119    9 Complete 6/11/2016 13:36 
120    10 In Progress 6/11/2016 13:48 
121    1 In Progress 6/11/2016 14:00 
122    2 Complete 6/11/2016 14:12 
123    3 Complete 6/11/2016 14:24 
124    4 Complete 6/11/2016 14:36 
125    6 In Progress 6/11/2016 14:48 

需要分配给现有员工匹配的类型没有活动正在进行 如果一切都在进步，如果返回0 多于一个可用，分配给分配的一个。

那么如果下一个指派是文书工作，请不要拿1或10，因为进行中， 而是拿走最后第一个完成的2,5,7,8,9（其他未被占用的）文员8（跳过5不可用）。

如果下一个分配是技术性的，它应该去约翰，如果约翰不可用，应该返回null。

写这样的事情至今：

function: NextEmployee('Clerical') 

Select EmployeeID from Employees e , Activities a where e.available= 1 and e.type='Clerical' and 

Answer 1

该代码使用window function找到每个员工的最后一个活动，然后检查，本次活动有“完成”状态：

DECLARE @Type VARCHAR(20) 
SET @Type = 'Clerical' 
SELECT TOP 1 TaskType, EmployeesID, Task_DateTime 
FROM(
    SELECT E.EmployeesID, E.TaskType, Task_DateTime, ActivityStatus, ROW_NUMBER() OVER(PARTITION BY A.EmployeesID ORDER BY Task_DateTime DESC) AS LatestEmploeeTask 
    FROM #Activities AS A 
     INNER JOIN #Employees AS E ON A.EmployeesID = E.EmployeesID 
    WHERE Available = 1) AS a 
WHERE LatestEmploeeTask = 1 AND ActivityStatus = 'Complete' 
    AND TaskType = @Type 
ORDER BY Task_DateTime ASC 

我不得不将样本数据转换成可用的表格数据。代码如下：

CREATE TABLE #Employees(EmployeesID INT, UserName VARCHAR(10), TaskType VARCHAR(20), Available BIT) 
CREATE TABLE #Activities(ActivityID INT, EmployeesID INT, ActivityStatus VARCHAR(20), Task_DATETIME DATETIME) 

INSERT INTO #Employees 
VALUES 
(1, 'Bill', 'Clerical', 1), 
(2, 'Ann', 'Clerical', 1), 
(3, 'John', 'Technical', 1), 
(4, 'Jack', 'Technical', 0), 
(5, 'Mary', 'Clerical', 0), 
(6, 'Sam', 'Technical', 0), 
(7, 'Mark', 'Clerical', 1), 
(8, 'Andy', 'Clerical', 1), 
(9, 'Rick', 'Clerical', 1), 
(10, 'Nancy', 'Clerical', 1) 

INSERT INTO #Activities 
VALUES 
(101, 2, 'Complete', '6/11/2016 10:00'), 
(102, 1, 'Complete', '6/11/2016 10:12'), 
(103, 3, 'Complete', '6/11/2016 10:24'), 
(104, 4, 'Complete', '6/11/2016 10:36'), 
(105, 7, 'Complete', '6/11/2016 10:48'), 
(106, 6, 'Complete', '6/11/2016 11:00'), 
(107, 5, 'Complete', '6/11/2016 11:12'), 
(108, 8, 'Complete', '6/11/2016 11:24'), 
(109, 10, 'Complete', '6/11/2016 11:36'), 
(110, 9, 'Complete', '6/11/2016 11:48'), 
(111, 5, 'Complete', '6/11/2016 12:00'), 
(112, 8, 'Complete', '6/11/2016 12:12'), 
(113, 3, 'Complete', '6/11/2016 12:24'), 
(114, 4, 'Complete', '6/11/2016 12:36'), 
(115, 1, 'Complete', '6/11/2016 12:48'), 
(116, 6, 'Complete', '6/11/2016 13:00'), 
(117, 7, 'Complete', '6/11/2016 13:12'), 
(118, 2, 'Complete', '6/11/2016 13:24'), 
(119, 9, 'Complete', '6/11/2016 13:36'), 
(120, 10, 'In Progress', '6/11/2016 13:48'), 
(121, 1, 'In Progress', '6/11/2016 14:00'), 
(122, 2, 'Complete', '6/11/2016 14:12'), 
(123, 3, 'Complete', '6/11/2016 14:24'), 
(124, 4, 'Complete', '6/11/2016 14:36'), 
(125, 6, 'In Progress', '6/11/2016 14:48')