SQL Server Comparing Subsequent Rows for Duplicates

Question

Hi, I am trying to write a SQL Server query but have had no luck and was wondering if anyone may have any ideas on how to achieve my query.

What i'm trying to do:

I have a table with several columns naming the ones that i am dealing with TaskID, StatusCode, Timestamp. Now this table just holds tasks for one of our systems that run throughout the day and when something runs it gets a timestamp and the statuscode depending on the status for that task.

Sometimes what happens is the task table will be updated with a new timestamp but the statusCode will not have changed since the last update of the task so for two or more consecutive rows of a given task the statusCode can be the same. When i say consecutive rows i mean with regards to timestamp.

So example task 88 could have twenty rows at statusCode 2 after which the status code changes to something else.

Now what i am trying to do with no luck at the moment is to retrieve a list from this table of all the tasks and the statuscodes and the timestamps but in the case where i have more than one consecutive row for a task with the same statuscode i just want to take the first row with the lowest timestamp and ignore the rest of the row until the statuscode for that task changes.

To make it simpler in this case you can assume that i have a taskid which i am filtering on so i am just looking at a single task.

Does anyone have any ideas as to how i can do this or perhaps something that i coudl probably read to help me?

Thanks Irfan.

Answer 1

Something like

select TaskID,StatusCode,Min(TimeStamp)
from table
group by TaskID,StatusCode
order by 1,2

Note that is statuscode can duplicate, you will need an additional field, but hopefully this can point you in the right direction...

Answer 2

Something like the following should get you in the right direction....

CREATE TABLE #T
(
    TaskId INT
    ,StatusCode INT
    ,StatusTimeStamp DATETIME
)

INSERT INTO #T
SELECT 1, 1, '2009-12-01 14:20'
UNION SELECT 1, 2, '2009-12-01 16:20'
UNION SELECT 1, 2, '2009-12-02 09:15'
UNION SELECT 1, 2, '2009-12-02 12:15'
UNION SELECT 1, 3, '2009-12-02 18:15'

;WITH CTE AS
(
SELECT TaskId
     ,StatusCode
     ,StatusTimeStamp
     ,ROW_NUMBER() OVER (PARTITION BY TaskId, StatusCode ORDER BY TaskId, StatusTimeStamp DESC)  AS RNUM
FROM  #T
)
SELECT TaskId
     ,StatusCode
     ,StatusTimeStamp
FROM CTE
WHERE RNUM = 1

DROP TABLE #T

Answer 3

This are a couple ways of getting what you want:

SELECT
     T1.task_id,
     T1.status_code,
     T1.status_timestamp
FROM
     My_Table T1
LEFT OUTER JOIN My_Table T2 ON
     T2.task_id = T1.task_id AND
     T2.status_timestamp < T1.status_timestamp
LEFT OUTER JOIN My_Table T3 ON
     T3.task_id = T1.task_id AND
     T3.status_timestamp < T1.status_timestamp AND
     T3.status_timestamp > T2.status_timestamp
WHERE
     T3.task_id IS NULL AND
     (T2.status_code IS NULL OR T2.status_code <> T1.status_code)
ORDER BY
     T1.status_timestamp

or

SELECT
     T1.task_id,
     T1.status_code,
     T1.status_timestamp
FROM
     My_Table T1
LEFT OUTER JOIN My_Table T2 ON
     T2.task_id = T1.task_id AND
     T2.status_timestamp = (
          SELECT
               MAX(status_timestamp)
          FROM
               My_Table T3
          WHERE
               T3.task_id = T1.task_id AND
               T3.status_timestamp < T1.status_timestamp)
WHERE
     (T2.status_code IS NULL OR T2.status_code <> T1.status_code)
ORDER BY
     T1.status_timestamp

Both methods rely on there being no exact matches of the status_timestamp values (two rows can't have the same exact status_timestamp for a given task_id.)