August 22, 2014 at 2:09 pm
CREATE TABLE #tblTasks
(
TaskID int,
TaskDate smalldatetime,
OrderID int
)
GO
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 11 2014', 3 UNION ALL
SELECT 13, 'Jan 16 2014', 4
SELECT * FROM #tblTasks ORDER BY OrderID
DROP TABLE #tblTasks
At the moment, the dates in that table are sequential. If, for example, someone updates the second row to be Jan 12 2014 - it will be later than the next row. If they changed it to Dec 31 2013 it would be earlier than the row before it.
I need a way of determining if the dates are in order - when the table is ordered by Order ID. I can do this by putting the rows into a cursor and looping through it sticking the dates into variables and comparing them. Is there a more elegant way of doing this?
August 22, 2014 at 2:25 pm
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
August 22, 2014 at 2:46 pm
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.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 22, 2014 at 2:49 pm
sku370870 (8/22/2014)
CREATE TABLE #tblTasks
(
TaskID int,
TaskDate smalldatetime,
OrderID int
)
GO
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 11 2014', 3 UNION ALL
SELECT 13, 'Jan 16 2014', 4
SELECT * FROM #tblTasks ORDER BY OrderID
DROP TABLE #tblTasks
At the moment, the dates in that table are sequential. If, for example, someone updates the second row to be Jan 12 2014 - it will be later than the next row. If they changed it to Dec 31 2013 it would be earlier than the row before it.
I need a way of determining if the dates are in order - when the table is ordered by Order ID. I can do this by putting the rows into a cursor and looping through it sticking the dates into variables and comparing them. Is there a more elegant way of doing this?
Your concept here seems a little flawed. What would you do if you needed to insert a row for Jan 14, 2014? You would have to update all rows with an OrderID > that date to allow for the new row to be inserted. You can use some logic to determine if all the dates are sequential using a number of various methods. The real question is what are you really trying to do here?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 22, 2014 at 2:49 pm
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.
August 22, 2014 at 3:10 pm
If a row gets deleted, or added, I will re-order. Thank you for your answer.
August 22, 2014 at 3:15 pm
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.
August 22, 2014 at 3:26 pm
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. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 22, 2014 at 3:54 pm
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.
August 22, 2014 at 3:57 pm
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
August 22, 2014 at 4:21 pm
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
That's what I need. Thank you very much.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply