Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

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

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Select previous record information
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ccbuilder
Starting Member

USA
17 Posts

Posted - 06/04/2013 :  15:38:40  Show Profile  Reply with Quote
I have three tables

Request
[RID] pk int auto,
[Description] varchar(200) not null,

Request_Dates
[DateID] pk int auto,
[Date] smalldatetime not null,
[RID] fk int not null

Details
[RowID] pk int auto,
[DateID] fk int not null,
[NumericalOrder] tinyint not null,
[Address_From] varchar(100) not null,
[Address_To] varchar(100) not null

I also have such data from the following query
SELECT Request_Dates.DateID, Request_Dates.[Date], 
       Details.RowID, NumericalOrder, Address_From, Address_To
FROM Request_Dates INNER JOIN Details ON 
     Request_Dates.DateID = Details.DateID
WHERE Request_Dates.RID=1
ORDER BY [Date], NumericalOrder
DateID  Date        RowID  NumericalOrder  Address_From       Address_To
1       2013-06-01  1      1               123 Elm Street     457 Langley Ave
1       2013-06-01  13     2                                  263 Oak St
1       2013-06-01  58     3                                  1004 Pine Rd
108     2013-06-04  581    1               457 W. St Francis  800 E Montana
108     2013-06-04  590    2                                  1515 N 16th St
The query I would like help with is one where the address_to from the previous row is the Address_From for the next row
In this example, the first row will have no prior address_to, but the second row would have to read: Address_From: 457 Langley Ave --- Address_To: 263 Oak Rd

The RowID column items are not sequential because users delete rows from their data entry. What is sequential for each date is the NumericalOrder column. A given date can have many NumericalOrders which always begin with 1

Your assistance is mostly appreciated.

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 06/04/2013 :  16:49:32  Show Profile  Reply with Quote
quote:
Originally posted by ccbuilder

I have three tables

Request
[RID] pk int auto,
[Description] varchar(200) not null,

Request_Dates
[DateID] pk int auto,
[Date] smalldatetime not null,
[RID] fk int not null

Details
[RowID] pk int auto,
[DateID] fk int not null,
[NumericalOrder] tinyint not null,
[Address_From] varchar(100) not null,
[Address_To] varchar(100) not null

I also have such data from the following query
SELECT Request_Dates.DateID, Request_Dates.[Date], 
       Details.RowID, NumericalOrder, Address_From, Address_To
FROM Request_Dates INNER JOIN Details ON 
     Request_Dates.DateID = Details.DateID
WHERE Request_Dates.RID=1
ORDER BY [Date], NumericalOrder
DateID  Date        RowID  NumericalOrder  Address_From       Address_To
1       2013-06-01  1      1               123 Elm Street     457 Langley Ave
1       2013-06-01  13     2                                  263 Oak St
1       2013-06-01  58     3                                  1004 Pine Rd
108     2013-06-04  581    1               457 W. St Francis  800 E Montana
108     2013-06-04  590    2                                  1515 N 16th St
The query I would like help with is one where the address_to from the previous row is the Address_From for the next row
In this example, the first row will have no prior address_to, but the second row would have to read: Address_From: 457 Langley Ave --- Address_To: 263 Oak Rd

The RowID column items are not sequential because users delete rows from their data entry. What is sequential for each date is the NumericalOrder column. A given date can have many NumericalOrders which always begin with 1

Your assistance is mostly appreciated.

SELECT
	r.DateID,
	r.[Date],
	d.RowID,
	d.NumericalOrder,
	COALESCE(NULLIF(d.Address_From,''),dp.Address_To) AS Address_From,
	d.AddressTo
FROM
	Request_Dates r
	INNER JOIN Details d ON d.DateId = r.DateID
	LEFT JOIN Details dp ON dp.DateId = d.DateId AND dp.RowId = d.RowID-1
ORDER BY
	r.Date, d.NumericalOrder;
Go to Top of Page

ccbuilder
Starting Member

USA
17 Posts

Posted - 06/04/2013 :  17:11:17  Show Profile  Reply with Quote
Thanks for the prompt reply.

The query works when RowID is sequential like in the case of DateID=108; however, with DateID=1, I get NULL for Address_From
Users can delete and insert rows from other instances so RowID cannot be expected to be sequential.

Thanks in advance
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 06/04/2013 :  17:23:59  Show Profile  Reply with Quote
quote:
Originally posted by ccbuilder

Thanks for the prompt reply.

The query works when RowID is sequential like in the case of DateID=108; however, with DateID=1, I get NULL for Address_From
Users can delete and insert rows from other instances so RowID cannot be expected to be sequential.

Thanks in advance

Ah, I had missed that - sorry about that.
;WITH cte AS
(
	SELECT *,
		ROW_NUMBER() OVER (PARTITION BY DateId ORDER BY RowId) AS NewRowId
	FROM
		Details
)
SELECT
	r.DateID,
	r.[Date],
	d.RowID,
	d.NumericalOrder,
	COALESCE(NULLIF(d.Address_From,''),dp.Address_To) AS Address_From,
	d.AddressTo
FROM
	Request_Dates r
	INNER JOIN cte d ON d.DateId = r.DateID
	LEFT JOIN cte dp ON dp.DateId = d.DateId AND dp.NewRowId = d.NewRowID-1
ORDER BY
	r.Date, d.NumericalOrder;
Go to Top of Page

ccbuilder
Starting Member

USA
17 Posts

Posted - 06/04/2013 :  18:00:41  Show Profile  Reply with Quote
Awesome, it works.

Thank you so much James :)
Go to Top of Page
  Previous Topic Topic Next 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.12 seconds. Powered By: Snitz Forums 2000