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

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;

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

---

;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;