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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Select previous record information
 New Topic  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

3760 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

3760 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  
 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.22 seconds. Powered By: Snitz Forums 2000