CODEWITHSUNDEEP
sqlsql-server-2008iteration
Thracian
Thracian

Reputation: 661

Iterating through rows to capture the value in the next row

I have been a long time reader of this forum. It has helped me a lot, however I have a question which I cannot find a solution specific to my requirements.

I am given the task to develop a metric to determine how many days the 'Staff Performance Evaulations' are past due. The data comes in the following format:

EmployeeID LastEvalCompleteDate NextEvalDueDate
1001       2010-01-01           2010-11-01
1001       2010-11-20           2011-11-01
1001       2011-10-29           2012-11-15
1002       NULL                 2013-12-01

According to the sample data above, the employee 1001 has had 3 evals since 2010-01-01. Employee 1002 has started this year and his first eval is due on 2013-12-01.

What I need to do is to convert the data to this format:

EmployeeID EvalDueDate EvalCompleteDate DaysPastDue
1001       2010-11-01  2010-11-20       19
1001       2011-11-01  2011-10-29       -2
1001       2012-11-15  NULL             342 (based on today's date)
1002       2013-12-01  NULL             -39 (based on today's date)

As you noticed, I derive a new row by taking the value of NextEvalDueDate column and mapping it to the EvalDueDate column in my new table. I also take the value in the LastEvalCompleteDate column in the NEXT row and map it to the NextEvalDueDate column.

I am having trouble with iterating through the rows for a given EmployeeID. I tried using ROW_NUMBER() OVER (PARTITION BY ...) but it did not take me anywhere.

I appreciate any kind of help. Thank you.

Upvotes: 2

Views: 119

Answers (2)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

DECLARE @Results TABLE
(
    EmployeeID INT NOT NULL,    
    RowNum INT NOT NULL,        
        PRIMARY KEY (RowNum, EmployeeID),
    LastEvalCompleteDate DATE,
    NextEvalDueDate DATE
);
INSERT  @Results (RowNum, EmployeeID, LastEvalCompleteDate, NextEvalDueDate)
SELECT  ROW_NUMBER() OVER(PARTITION BY e.EmployeeID ORDER BY e.LastEvalCompleteDate),
        e.EmployeeID,
        e.LastEvalCompleteDate,
        e.NextEvalDueDate
FROM    dbo.EmployeeEvaluation e;

WITH Base
AS
(
SELECT  crt.RowNum,
        crt.EmployeeID,
        crt.NextEvalDueDate AS EvalDueDate,
        nxt.LastEvalCompleteDate AS EvalCompleteDate 
FROM    @Results crt
LEFT JOIN @Results nxt ON crt.EmployeeID = nxt.EmployeeID AND crt.RowNum + 1 = nxt.RowNum
)
SELECT  r.*,
        DATEDIFF(DAY, r.EvalDueDate, ISNULL(r.EvalCompleteDate, GETDATE())) AS DaysPastDue
FROM    Base r
ORDER BY r.EmployeeID, r.RowNum

Upvotes: 0

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

You went into right direction using ROW_NUMBER() OVER (PARTITION BY ...). Don't know where have you stuck, but it should be something like this:

WITH CTE AS 
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY NextEvalDueDate) RN
    FROM dbo.Table1
)
SELECT 
    c1.EmployeeID
  , c1.NextEvalDueDate AS EvalDueDate
  , c2.LastEvalCompleteDate AS EvalCompleteDate 
  , DATEDIFF(DAY, c1.NextEvalDueDate, COALESCE(c2.LastEvalCompleteDate, GETDATE())) AS DaysPastDue
FROM CTE c1
LEFT JOIN CTE c2 ON c1.EmployeeID = c2.EmployeeID AND c1.RN = c2.RN - 1
ORDER BY c1.EmployeeID, c1.RN

Upvotes: 1

Related Questions