September 22, 2008 at 8:51 am
Hi
I have a result set which contains a field showing the date a change has been made to a record, a field showing addresses. The dates do not neccessarily reflect when a change in address was made so you could have rows and rows where the same address is repeated.
I therefore require help to identify the date the latest address change was made.
Any help would be greatly appreciated.
Thanks
September 22, 2008 at 8:58 am
HI there,
Please could you read the link below to get the best help from here.
http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 22, 2008 at 8:59 am
Unfortunately, we can't really help you with just the info provided. Please take the time to read this article http://www.sqlservercentral.com/articles/Best+Practices/61537/.
If you follow its advice, and post additional information regarding your problem, we can try and help you better.
😎
September 22, 2008 at 9:28 am
What did you mean by "The dates do not necessarily reflect when a change in address was made so you could have rows and rows where the same address is repeated" ?
All I understand so far is you have two fields -"Date Changed" and "Address". I would defenetly be able to help you more if you provide more explanation. Like everyone suggested, it sure would not hurt to visit http://www.sqlservercentral.com/articles/Best+Practices/61537/ and get some thought on providing more information.
§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§
always Happy, always Prasanna 🙂
September 23, 2008 at 12:41 pm
When i tried to solve this based on just a table with (rownumber, addressident, vchaddress, datechanged), I ran into grouping problems. I could try to find the max rownumber per addressident, then take that address and find the lowest rownumber for this address. The assumtion is that the last rownumber for a given addressident would be the newest address, then i find the lowest rownumber for that address which should return the date it changed. BUT, lets say i lived at 1234 park st, then moved to 2345 park ave and had several inserts inbetween
eg.
1,1,1234 park st,1-1-2000
2,1,1234 park st,1-2-2000
3,1,1234 park st,1-3-2000
4,1,2345 park ave,1-4-2000
But now I move back to that original address
eg.
1,1,1234 park st,1-1-2000
2,1,1234 park st,1-2-2000
3,1,1234 park st,1-3-2000
4,1,2345 park ave,1-4-2000
5,1,1234 park st,1-5-2000
the min rownumber will be 1 and not the expected 4.
might be better finding the newest entry, grabbing the address and working your way backwards with a cursor until the address changes.
I can't think of using a set based solution to this only because of the possibility of moving back to the original address.
I would be interested in a set based solution for this problem for a purely thinking exercise
September 23, 2008 at 12:56 pm
bcronce (9/23/2008)
When i tried to solve this based on just a table with (rownumber, addressident, vchaddress, datechanged), I ran into grouping problems. I could try to find the max rownumber per addressident, then take that address and find the lowest rownumber for this address. The assumtion is that the last rownumber for a given addressident would be the newest address, then i find the lowest rownumber for that address which should return the date it changed. BUT, lets say i lived at 1234 park st, then moved to 2345 park ave and had several inserts inbetweeneg.
1,1,1234 park st,1-1-2000
2,1,1234 park st,1-2-2000
3,1,1234 park st,1-3-2000
4,1,2345 park ave,1-4-2000
But now I move back to that original address
eg.
1,1,1234 park st,1-1-2000
2,1,1234 park st,1-2-2000
3,1,1234 park st,1-3-2000
4,1,2345 park ave,1-4-2000
5,1,1234 park st,1-5-2000
the min rownumber will be 1 and not the expected 4.
might be better finding the newest entry, grabbing the address and working your way backwards with a cursor until the address changes.
I can't think of using a set based solution to this only because of the possibility of moving back to the original address.
I would be interested in a set based solution for this problem for a purely thinking exercise
I am really confused by your logic. Looking at the first set of data, I would say the address changed on 1-4-2000, as all previous address are different than the one in record 4.
Perhaps you need to clarify better what it is you are trying to accomplish. I'd suggest reading the article referneced above as well. The more information you provide regarding the problem, the better help you will get.
😎
September 23, 2008 at 1:27 pm
1,1,1234 park st,1-1-2000
2,1,1234 park st,1-2-2000
3,1,1234 park st,1-3-2000
4,1,2345 park ave,1-4-2000
5,1,1234 park st,1-5-2000
6,1,1234 park st,1-6-2000
The correct result should be 5.
originally my set based logic would return the correct result for anything like this
1,1,1234 park st,1-1-2000
2,1,1234 park st,1-2-2000
3,1,1234 park st,1-3-2000
4,1,2345 park ave,1-4-2000
5,1,2345 park ave,1-5-2000
6,1,2345 boardwalk,1-6-2000
7,1,2345 boardwalk,1-7-2000
which the result is 6.
first I'd grab the last row number for ident 1 which is row 7. Then I group by "2345 boardwalk" for ident 7 and grab the min rownumber which is 6.
The problem is the set based, atleast the way i thought up, breaks down when you move back to the same address
1,1,1234 park st,1-1-2000
2,1,1234 park st,1-2-2000
3,1,1234 park st,1-3-2000
4,1,2345 park ave,1-4-2000
5,1,2345 park ave,1-5-2000
6,1,2345 boardwalk,1-6-2000
7,1,2345 boardwalk,1-7-2000
8,1,1234 park st,1-8-2000
since my set based way would grab the last row, which is 8, group by "1234 park st" and grab the min row, which is 1. that's the wrong date.
The cursor sultion i though of would grab the last row, which is 8, and steps through each previous row until is see a change in address, which would stop on 7, but then you grab the row you compared before that because it will end on the last row of the previous address and not return the row of the current address.
There's more than one way to skin a caat, and I'd like to see a better approach so I can learn a better way of solving this sort of issue.
September 23, 2008 at 2:41 pm
bcronce (9/23/2008)
1,1,1234 park st,1-1-20002,1,1234 park st,1-2-2000
3,1,1234 park st,1-3-2000
4,1,2345 park ave,1-4-2000
5,1,1234 park st,1-5-2000
6,1,1234 park st,1-6-2000
The correct result should be 5.
originally my set based logic would return the correct result for anything like this
1,1,1234 park st,1-1-2000
2,1,1234 park st,1-2-2000
3,1,1234 park st,1-3-2000
4,1,2345 park ave,1-4-2000
5,1,2345 park ave,1-5-2000
6,1,2345 boardwalk,1-6-2000
7,1,2345 boardwalk,1-7-2000
which the result is 6.
first I'd grab the last row number for ident 1 which is row 7. Then I group by "2345 boardwalk" for ident 7 and grab the min rownumber which is 6.
The problem is the set based, atleast the way i thought up, breaks down when you move back to the same address
1,1,1234 park st,1-1-2000
2,1,1234 park st,1-2-2000
3,1,1234 park st,1-3-2000
4,1,2345 park ave,1-4-2000
5,1,2345 park ave,1-5-2000
6,1,2345 boardwalk,1-6-2000
7,1,2345 boardwalk,1-7-2000
8,1,1234 park st,1-8-2000
since my set based way would grab the last row, which is 8, group by "1234 park st" and grab the min row, which is 1. that's the wrong date.
The cursor sultion i though of would grab the last row, which is 8, and steps through each previous row until is see a change in address, which would stop on 7, but then you grab the row you compared before that because it will end on the last row of the previous address and not return the row of the current address.
There's more than one way to skin a caat, and I'd like to see a better approach so I can learn a better way of solving this sort of issue.
How is this being used? Knowing that will help find a different way to skin the cat.
😎
September 23, 2008 at 3:11 pm
I'm not sure.. My 'answer' is based on what little info we got up top.
September 23, 2008 at 3:12 pm
Here is another way to skin a cat. I'm sure some of the gurus out there may come back and say this is still rebar, but it eliminates a cursor.
create table #TestTab (
RowNum int,
AddressIdent int,
Address1 varchar(50),
DateChanged datetime);
insert into #TestTab (RowNum, AddressIdent, Address1, DateChanged)
select 1,1,'1234 park st','1-1-2000' union all
select 2,1,'1234 park st','1-2-2000' union all
select 3,1,'1234 park st','1-3-2000' union all
select 4,1,'2345 park ave','1-4-2000' union all
select 5,1,'2345 park ave','1-5-2000' union all
select 6,1,'2345 boardwalk','1-6-2000' union all
select 7,1,'2345 boardwalk','1-7-2000' union all
select 8,1,'1234 park st','1-8-2000';
select
tt1.*,
tt2.DateChanged
from
#TestTab tt1
inner join #TestTab tt2
on (tt2.RowNum = isnull((select max(tt3.RowNum) from #TestTab tt3 where tt3.RowNum tt1.Address1),0) + 1)
order by
tt1.RowNum;
drop table #TestTab;
Edit: Code didn't paste correctly, please stand by....
😎
September 23, 2008 at 3:19 pm
create table #TestTab (
RowNum int,
AddressIdent int,
Address1 varchar(50),
DateChanged datetime);
insert into #TestTab (RowNum, AddressIdent, Address1, DateChanged)
select 1,1,'1234 park st','1-1-2000' union all
select 2,1,'1234 park st','1-2-2000' union all
select 3,1,'1234 park st','1-3-2000' union all
select 4,1,'2345 park ave','1-4-2000' union all
select 5,1,'2345 park ave','1-5-2000' union all
select 6,1,'2345 boardwalk','1-6-2000' union all
select 7,1,'2345 boardwalk','1-7-2000' union all
select 8,1,'1234 park st','1-8-2000';
select
tt1.*,
tt2.DateChanged as AddressChanged
from
#TestTab tt1
inner join #TestTab tt2
on (tt2.RowNum = isnull((select
max(tt3.RowNum)
from
#TestTab tt3
where
tt3.RowNum < tt1.RowNum
and tt3.AddressIdent = tt1.AddressIdent -- Added code
and tt3.Address1 <> tt1.Address1),0) + 1)
order by
tt1.RowNum;
drop table #TestTab;
Okay, this time the code looks better...
Edit: Other than I have ANOTHER error in the code. It works great for a single AddressIdent.
😎
September 24, 2008 at 8:42 am
Here's the solution for a single addressident:
DECLARE @ADDRESSES TABLE (
rownumber int,
addressident int,
vchaddress varchar(15),
datechanged DateTime
PRIMARY KEY(addressident, rownumber)
)
INSERT INTO @ADDRESSES
SELECT 1,1,'1234 park st','1-1-2000' UNION ALL
SELECT 2,1,'1234 park st','1-2-2000' UNION ALL
SELECT 3,1,'1234 park st','1-3-2000' UNION ALL
SELECT 4,1,'2345 park ave','1-4-2000' UNION ALL
SELECT 5,1,'1234 park st','1-5-2000'
DECLARE @maxrn AS int, @lastaddress AS varchar(15), @choicern AS int
SELECT @maxrn = MAX(rownumber)
FROM @ADDRESSES
SELECT @lastaddress = vchaddress
FROM @ADDRESSES
WHERE rownumber = @maxrn
SELECT @choicern = MAX(rownumber)
FROM @ADDRESSES
WHERE vchaddress <> @lastaddress
SELECT *
FROM @ADDRESSES
WHERE rownumber = @choicern
To make this work for multiple addressidents, you need to replace the scalar variables with table variables, and add GROUP BYs to the mix.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 26, 2008 at 6:03 am
Let us take an example
Create the table with the below structure (having an identity column)
Create table Tbl_UserAddresses(
Sno int identity(1,1),User int,UserAddress Varchar(50),ChangedDate datetime)
Insert into Tbl_UserAddresses(User,UserAddress,ChangedDate)
select 20,'abc','09/01/08' union
select 21,'def','09/02/08' union
select 20,'ghi','09/01/08' union
select 20,'jkl','09/02/08' union
select 21,'mno','09/07/08' union
select 20,'pqr','09/03/08'
The above one is the physical table.
Now your situation is to get the address without considering the changeddate as it doesnot indicate the exact date
Use the below script to generate the result
select * into #Tbl_Temp from Tbl_UserAddresses with(nolock)
delete a from #Tbl_Temp a with(nolocK),#Tbl_Temp b with(nolocK)
where a.user = b.user and a.sno < b.sno
select * from #Tbl_temp with(nolocK)
will give the desired result
withoout much complexity:)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply