CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Bill Anderson
Bill Anderson

Reputation: 51

Selecting employee based availability Query SQL Server 2012

I have two tables, one for employees and another of their current and past activities. I need a function/query that selects an employee for the next activity based on type. here is sample data:

Table Employees 

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

Activities 

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

Need to assign to available employee matching type without an activity in progress if all are in progress, return 0 if more than one available, assign to the one that was assigned.

So if next assign is clerical, do not take 1 or 10 since in progress, but take last first completed clerical of 2,5,7,8,9 (other unoccupied ones) which is 8 (skipping 5 since is not available).

If next assign is technical, it should go to John and if john is not available, should return null.

Wrote something like this so far:

function: NextEmployee('Clerical')

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

Upvotes: 2

Views: 116

Answers (1)

Alex
Alex

Reputation: 5157

This code uses a window function to find the last activity for each employee and then checks that this activity has 'Complete' status:

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

I had to convert the sample data into usable table data. The code is below:

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')

Upvotes: 2

Related Questions