Is this SQL Query possible?

Question

Suppose I have this data:

Employee  Task   IsCurrent
--------  ----   ---------
Jeff      1      Yes
Jeff      2      No
Jane      1      No
Jane      2      Yes
Jane      3      No
Jane      4      No
Jake      1      Yes

Employees have some number of tasks, and one of them will be current. The task number indicates an ordering -- they perform tasks in order, looping back to the beginning when they're done with the last one. I'd like a query to see the next three tasks. I'd like this result set:

Employee    CurrentTask  NextTask  NextTask2
--------    -----------  --------  ---------
Jeff        1            2         1
Jane        2            3         4
Jake        1            1         1

Is this possible? I'm using SQL 2005

UPDATE: Sorry, I didn't make clear that the task numbers aren't necessarily sequental -- i.e. Mike might actually have tasks 10, 23, and 223. So I can't just join looking for the next sequential task number.

Answer 1

Maybe not the best, but you can try this way, asuming there is no more than 2 NextTask's ;)

SELECT e.Employee, e.Task AS CurrentTask, 
(
    SELECT TOP 1 e1.Task FROM dbo.Employees AS e1 WHERE e1.Employee=e.Employee AND e1.Task > e.Task ORDER BY e1.Task ASC
) AS NextTask,
(
    SELECT TOP 1 e2.Task FROM dbo.Employees AS e2 WHERE e2.Employee=e.Employee AND e2.Task > (
        SELECT TOP 1 e1.Task FROM dbo.Employees AS e1 WHERE e1.Employee=e.Employee AND e1.Task > e.Task ORDER BY e1.Task ASC
    ) ORDER BY e2.Task ASC
) AS NextTask2
FROM dbo.Employees AS e
WHERE e.IsCurrent = 'Yes'

PS. maybe PIVOT would do the trick, but im not to good at it.

Answer 2

Shouldn't be too bad a stored proc with the following steps

Create a temp table with the columns Employee, CurrentTask, NextTask, and NextTask2 Insert records with each employee and their CurrentTask. This is a simple SQL statement... and you could combine steps 1 and 2.

INSERT INTO #tempTable (Employee, CurrentTask) VALUES Employee, Task WHERE IsCurrent = 'Yes'

Then You'll need to loop through these records, progressively updating the NextTask and NextTask2 columns using a combination of TOP 1 and Order By Task ASC

Answer 3

If you are only interested in the next and the one after that, you can use the following:

SELECT t.employee,
       t.task AS current_task,
       t1.task AS next_task, 
       t2.task AS next_task_2
FROM tasks t
  LEFT JOIN tasks t1 ON t1.task = t.task + 1 AND t1.employee = t.employee
  LEFT JOIN tasks t2 ON t2.task = t1.task + 1 AND t2.employee = t1.employee
WHERE t.isCurrent = 'yes'

Answer 4

Use:

WITH summary AS (
   SELECT t.employee,
          t.task,
          t.iscurrent,
          ROW_NUMBER() OVER (PARTITION BY t.employee
                                 ORDER BY t.task) AS rank
     FROM TABLE t)
   SELECT a.employee,
          a.task AS current_task,
          COALESCE(b.task, y.min_task) AS next_task,
          COALESCE(c.task, y.min_task) AS next_task2
     FROM summary a
     JOIN (SELECT x.employee,
                  MIN(x.task) AS min_task
             FROM TABLE x
         GROUP BY x.employee) y ON y.employee = a.employee
LEFT JOIN summary b ON b.employee = a.employee
                   AND b.rank = a.rank + 1
LEFT JOIN summary c ON c.employee = a.employee
                   AND c.rank = a.rank + 2                   
    WHERE a.iscurrent = 'yes'

Like I said earlier, would be easier if SQL Server supported LEAD/LAG functions...

Answer 5

This is a two-part problem. First, you have to solve the modulus problem (listing tasks in order, starting with the current task):

Step 1: build a query whose results look like this (I've altered the data as per your edit)

Employee  IsCurrent OriginalTask [Task+MaxTask] as Task
--------  ----      ------------ ---------------------- 
Jeff      Yes       37           15
Jeff      No        44           22
Jane      No        55           13
Jane      Yes       67           25
Jane      No        73           31
Jane      No        84           42
Jake      Yes       38           19

Step 2: do a UNION with the original query

Employee  IsCurrent Task OriginalTask
--------  ----      ---- ------------
Jeff      Yes       15   15
Jeff      No        22   22
Jeff      Yes       37   15
Jeff      No        44   22
Jane      No        13   13
Jane      Yes       25   25
Jane      No        31   31
Jane      No        42   42
Jane      No        55   13
Jane      Yes       67   25
Jane      No        73   31
Jane      No        84   42
Jake      Yes       19   19
Jake      Yes       38   19

Step 3: Suppress rows where task# < lowest current or task# >= highest current

Employee  IsCurrent Task OriginalTask
--------  ----      ---- ------------ 
Jeff      Yes       15   15
Jeff      No        22   22
Jane      Yes       25   25
Jane      No        31   31
Jane      No        42   42
Jane      No        55   13
Jake      Yes       19   19

Now you have the modulus data, and an ordinary pivot-problem.

Answer 6

WITH EmpData AS
(
SELECT 
 Employee, Task, IsCurrent, 
 ROW_NUMBER() OVER (PARTITION BY Employee ORDER BY Task) AS RN,
 CASE WHEN COUNT(*) OVER (PARTITION BY Employee) = ROW_NUMBER() OVER (PARTITION BY Employee ORDER BY Task) THEN 1 ELSE 0 END AS LastTask
 FROM Employees
)
,
EmployeeTasks AS
(
SELECT Employee, Task, 1 AS LEVEL, RN, LastTask
FROM EmpData 
WHERE IsCurrent = 'Yes'
UNION ALL
SELECT e.Employee, e.Task, et.Level + 1, e.RN, e.LastTask
FROM EmpData e JOIN EmployeeTasks et ON e.Employee = et.Employee AND e.RN = CASE WHEN et.LastTask = 1 THEN 1 ELSE et.RN+1 END AND LEVEL<3)

SELECT Employee,[1] AS CurrentTask, [2] AS NextTask, [3] AS NextTask2
FROM
(SELECT Employee, LEVEL, task FROM  EmployeeTasks) e
PIVOT
(
MAX(task)
FOR LEVEL IN ([1], [2], [3])
) AS PivotTable