CODEWITHSUNDEEP
sqlsql-server-2008t-sql
user1707100
user1707100

Reputation: 1

TSQL Self Join: Exclude consecutive ID records having set criteria

I have a table as below:

ID, DATE, STATUS
1, 01JAN2012, STOP
1, 04JAN2012, RESTART
2, 01JAN2012, STOP-1
3, 10JAN2012, STOP
4, 15JAN2012, RESTART
5, 01JAN2012, STOP-1
5, 04JAN2012, STOP-2
5, 10JAN2012, STOP
5, 15JAN2012, RESTART

Expected Output:

ID, DATE, STATUS
2, 01JAN2012, STOP-1
3, 10JAN2012, STOP
4, 15JAN2012, RESTART
5, 01JAN2012, STOP-1
5, 04JAN2012, STOP-2

I want a SELECT statement to exclude all records with same ID having (STATUS='RESTART') and one consecutive previous record, if available.

Upvotes: 0

Views: 281

Answers (1)

GilM
GilM

Reputation: 3771

I'm not exactly sure what would happen if there were two consecutive RESTARTs, but barring such complications I think you might want something like this:

;WITH myTable AS(
SELECT *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [DATE]) AS rn
FROM OriginalTable
)
SELECT this.ID, this.DATE, this.STATUS
FROM myTable this
LEFT OUTER JOIN myTable prev ON prev.ID = this.ID and prev.rn = this.rn-1
LEFT OUTER JOIN myTable nxt ON nxt.ID = this.ID and nxt.rn = this.rn+1
WHERE NOT ((this.STATUS= 'RESTART' AND prev.STATUS IS NOT NULL)
           OR (COALESCE(nxt.STATUS,'') = 'RESTART'))

I was assuming that your DATE column is of type date or datetime, but if it's a string, you should convert it to a date in the ORDER BY portion of the ROW_NUMBER call.

Upvotes: 3

Related Questions