October 10, 2013 at 1:57 pm
I have a table that contains dates and times. For example fields are Date, ExTime, NewTime, Status. I am ordering them based on a expkey field that makes them show in the right order. I am wanting to do a row by row comparison and compare the second row field of extime to the first row field NewTime. If extime < Newtime then I want to update status with a "1". And then travers through the table row by row where second row in the above example becomes the first and a new second is pull and used. Here is a sample of what I have now - but it is not hitting and working all all of the rows for some reason.
{UPDATE t
SET t.Status = 1
FROM MyTable t
CROSS APPLY (SELECT TOP 1 NewTime
FROM MyTable
WHERE ID = t.ID AND [Date] = t.[Date]
ORDER BY ExpKey) t1
WHERE t.Extime < t1.NewTime}
This is not hitting all the rows like I want it to. I have the where clause comparing fields ID and Date to insure that the rows are attached to the same person. If the ID or Dates are not the same it is not attached to the same person so I would not want to update the status. So basically if the ID of Row 2 = ID of Row 1 and Date of Row 2 = Date of Row 1 I want to compare extime of row 2 and see if it is less than newtime of Row 1 - if so then update the status field of row 2.
Any help in figuring out why this sort of works but not on all I would appriciate.
October 10, 2013 at 8:53 pm
Something like the following should do the trick
UPDATE t
SET t.Status = 1
FROM MyTable t
INNER JOIN MyTable t1
ON T1 .ID = (SELECT (MIN(t3.ID) FROM MyTable T3
WHERE T3.ID > t.ID)
WHERE t.Extime < t1.NewTime
October 13, 2013 at 6:56 pm
Without DDL and consumable sample data I won't venture a coded solution.
I do recommend that you look into the SQL 2012 LAG function: http://technet.microsoft.com/en-us/library/hh231256.aspx
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 14, 2013 at 2:32 pm
Do I need to sort/order the query to insure the records are in the right order for the comparisons?
October 14, 2013 at 6:14 pm
drknight88 (10/14/2013)
Do I need to sort/order the query to insure the records are in the right order for the comparisons?
Read up on the LAG function. Part of its usage is to specify how you want the rows ordered when you pick off the one you want.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 15, 2013 at 5:25 am
The lag clause looks like a nice way to achieve this - I didn't know that it existed until just now.
Dwain - Thanks for teaching me something new 🙂
October 15, 2013 at 5:44 am
happycat59 (10/15/2013)
The lag clause looks like a nice way to achieve this - I didn't know that it existed until just now.Dwain - Thanks for teaching me something new 🙂
Technically I think it is an "analytic function" and not a clause but you're welcome anyway.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply