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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Apply Value from Next Row to Current Row

Author  Topic 

Sesonic
Starting Member

3 Posts

Posted - 2012-10-08 : 09:55:04
Hi,
Just wondering if someone could help with the following query
in MS SQL Server 2005

Row_ID Start_Date End_Date Current_ID Max_ID_Per_Group
1 020620050001 020620050001 0000004 0000004
2 170720070001 Null 0000012 0000016
3 180720070001 Null 0000015 0000016
4 180720070001 180720070001 0000016 0000016
5 070420090001 Null 0000020 0000025
6 120420090001 Null 0000023 0000025
7 140420090001 Null 0000024 0000025
8 150420090001 150420090001 0000025 0000025

Given 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_Group
1 020620050001 020620050001 0000004 0000004
2 170720070001 180720070001 0000012 0000016
3 180720070001 180720070001 0000015 0000016
4 180720070001 180720070001 0000016 0000016
5 070420090001 120420090001 0000020 0000025
6 120420090001 140420090001 0000023 0000025
7 140420090001 150420090001 0000024 0000025
8 150420090001 150420090001 0000025 0000025


I'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.
Go to Top of Page

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.
--- 1
SELECT a.*,
a.End_Date Current_End_Date,
COALESCE(a.End_Date,b.Start_Date) AS New_End_Date
FROM
tbl a
LEFT JOIN tbl b ON
a.Row_ID + 1 = b.Row_ID;

--- 2
UPDATE 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;
Go to Top of Page

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.
--- 1
SELECT a.*,
a.End_Date Current_End_Date,
COALESCE(a.End_Date,b.Start_Date) AS New_End_Date
FROM
tbl a
LEFT JOIN tbl b ON
a.Row_ID + 1 = b.Row_ID;

--- 2
UPDATE 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 specifies
tbl 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 me
if this is a stupid question.

Many Thanks for your help.
Go to Top of Page

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

--- 1
SELECT a.*,
a.End_Date Current_End_Date,
COALESCE(a.End_Date,b.Start_Date) AS New_End_Date
FROM
IP_STAYS a
LEFT JOIN IP_STAYS b ON
a.Row_ID + 1 = b.Row_ID;

--- 2
UPDATE 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;
Go to Top of Page
   

- Advertisement -