Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Sesonic
Starting Member
3 Posts |
Posted - 2012-10-08 : 09:55:04
|
Hi, Just wondering if someone could help with the following queryin MS SQL Server 2005Row_ID Start_Date End_Date Current_ID Max_ID_Per_Group1 020620050001 020620050001 0000004 00000042 170720070001 Null 0000012 00000163 180720070001 Null 0000015 00000164 180720070001 180720070001 0000016 00000165 070420090001 Null 0000020 00000256 120420090001 Null 0000023 00000257 140420090001 Null 0000024 00000258 150420090001 150420090001 0000025 0000025Given the sample data above and starting at row 1, I need to insert the start date from the next column into the End_Date of the current Column where the value of the Current_ID is less than the value of the Max_ID_Per_Group.I need to end up with the following:Row_ID Start_Date End_Date Current_ID Max_ID_Per_Group1 020620050001 020620050001 0000004 00000042 170720070001 180720070001 0000012 00000163 180720070001 180720070001 0000015 00000164 180720070001 180720070001 0000016 00000165 070420090001 120420090001 0000020 00000256 120420090001 140420090001 0000023 00000257 140420090001 150420090001 0000024 00000258 150420090001 150420090001 0000025 0000025I've tried this a few different ways but can't seem to get this working, I'm wondering if anyone can help.Many Thanks,Paul |
|
Sesonic
Starting Member
3 Posts |
Posted - 2012-10-08 : 09:56:36
|
Sorry, This didn't display exactly as intended. Hope this is readable. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-08 : 10:28:37
|
Run the first query to see if the new end dates are as per your requirement. Ifyou are satisfied, run the second query to do the updates.--- 1SELECT a.*, a.End_Date Current_End_Date, COALESCE(a.End_Date,b.Start_Date) AS New_End_DateFROM tbl a LEFT JOIN tbl b ON a.Row_ID + 1 = b.Row_ID; --- 2UPDATE a SET a.End_Date = COALESCE(a.End_Date,b.Start_Date)FROM tbl a LEFT JOIN tbl b ON a.Row_ID + 1 = b.Row_ID; |
|
|
Sesonic
Starting Member
3 Posts |
Posted - 2012-10-08 : 10:48:50
|
quote: Originally posted by sunitabeck Run the first query to see if the new end dates are as per your requirement. Ifyou are satisfied, run the second query to do the updates.--- 1SELECT a.*, a.End_Date Current_End_Date, COALESCE(a.End_Date,b.Start_Date) AS New_End_DateFROM tbl a LEFT JOIN tbl b ON a.Row_ID + 1 = b.Row_ID; --- 2UPDATE a SET a.End_Date = COALESCE(a.End_Date,b.Start_Date)FROM tbl a LEFT JOIN tbl b ON a.Row_ID + 1 = b.Row_ID;
Many Thanks for the Reply, Can I calrify something:I only have one table called IP_STAYS, in this code it specifiestbl b and when i run the first part of the code i'm getting the following error:Invalid object name 'tbl'. How do I use this code for the IP_STAYS table, I dont have another table? I'm very new to this so forgive meif this is a stupid question.Many Thanks for your help. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-08 : 11:27:41
|
I was using "tbl" as a dummy name for the table because I didn't know what your actual table name was. In your query you would replace the "tbl" with your table name, which is IP_STAYS--- 1SELECT a.*, a.End_Date Current_End_Date, COALESCE(a.End_Date,b.Start_Date) AS New_End_DateFROM IP_STAYS a LEFT JOIN IP_STAYS b ON a.Row_ID + 1 = b.Row_ID; --- 2UPDATE a SET a.End_Date = COALESCE(a.End_Date,b.Start_Date)FROM IP_STAYS a LEFT JOIN IP_STAYS b ON a.Row_ID + 1 = b.Row_ID; |
|
|
|
|
|
|
|