Lynn Pettis (8/22/2014)

---

sku370870 (8/22/2014)

---

Sean Lange (8/22/2014)

---

sku370870 (8/22/2014)

---

Lynn Pettis (8/22/2014)

---

Sean Lange (8/22/2014)

---

brad.mason5 (8/22/2014)

---

Not sure if this is what you are looking for. This is assuming OrderID is the correct order you want.

SELECT TaskID

,TaskDate

,OrderID

,TrueOrderID = ROW_NUMBER() OVER(ORDER BY TaskDate ASC)

FROM #tblTasks

ORDER BY OrderID

Then you could compare the OrderID to TrueOrderID

That works until a row is deleted or for some other reason you have a gap in OrderID.

SELECT TaskID

,TaskDate

,OrderID

,OrderSeq = ROW_NUMBER() OVER (ORDER BY OrderID ASC)

,TrueOrderID = ROW_NUMBER() OVER(ORDER BY TaskDate ASC)

FROM #tblTasks

ORDER BY OrderID

Now compare OrderSeq to TrueOrderID.

Sorry, being a bit dense here. How do you compare OrderSeq to TrueOrderID? If I change the data to:

INSERT INTO #tblTasks (TaskID, TaskDate, OrderID)

SELECT 10, 'Jan 01 2014', 1 UNION ALL

SELECT 11, 'Jan 06 2014', 2 UNION ALL

SELECT 12, 'Jan 03 2014', 3 UNION ALL

SELECT 13, 'Jan 16 2014', 4

SELECT TaskID

,TaskDate

,OrderID

,OrderSeq = ROW_NUMBER() OVER (ORDER BY OrderID ASC)

,TrueOrderID = ROW_NUMBER() OVER(ORDER BY TaskDate ASC)

FROM #tblTasks

ORDER BY OrderID

This returns:

102014-01-01 00:00:00111

112014-01-06 00:00:00223

122014-01-03 00:00:00332

132014-01-16 00:00:00444

How can I determine that, in this group of records, there is at least one record (well, there will always be at least two) where OrderSeq <> TrueOrderID.

Ding Ding!!! If there are any rows where OrderSeq <> TrueOrderID then they are not in order. If they had been in order there would be no rows in that condition. 😀

Well, I did try

SELECT TaskID

,TaskDate

,OrderID

,ROW_NUMBER() OVER (ORDER BY OrderID ASC) AS [OrderSeq]

,ROW_NUMBER() OVER(ORDER BY TaskDate ASC) AS [TrueOrderID]

FROM #tblTasks

WHERE OrderSeq <> TrueOrderID

and it says that OrderSeq and TrueOrderID are invalid column names. So not sure how to reference them to find out if they are not equal. I also put a IF EXISTS() around the whole statement - so I could determine if the condition was met - and it reported that OrderBy could not be used in derived tables and a lot more.

Try:

WITH BaseData as (

SELECT TaskID

,TaskDate

,OrderID

,ROW_NUMBER() OVER (ORDER BY OrderID ASC) AS [OrderSeq]

,ROW_NUMBER() OVER(ORDER BY TaskDate ASC) AS [TrueOrderID]

FROM #tblTasks

)

SELECT * FROM BaseData

WHERE OrderSeq <> TrueOrderID