SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Apply Value from Next Row to Current Row		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sesonic
Starting Member

3 Posts
Posted - 10/08/2012 :  09:55:04  Show Profile  Reply with Quote
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
Edited by - Sesonic on 10/08/2012 10:03:12

Sesonic
Starting Member

3 Posts
Posted - 10/08/2012 :  09:56:36  Show Profile  Reply with Quote
Sorry, This didn't display exactly as intended. Hope this is readable.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 10/08/2012 :  10:28:37  Show Profile  Reply with Quote
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 - 10/08/2012 :  10:48:50  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 10/08/2012 :  11:27:41  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000