Help in Query?? [modified]
-
I need to get a value from previous row in result with any extracted row...:confused:? here's with an example
Table structure
empid EmpName Otherdetails
1 Robert abcd
2 Peter abcd
1 Bob abcdAbove given is history table with records added for any update or delete...so Robert was the record inserted to main table and Record was updated as bob. So we have two records in history table as above with same id and modifieddate to get order Now here Robert updated his name as Bob....now in result set I need to get a list of employees and their previous name like..
Id NewName OldName
1 Robert Null
2 Peter Null
1 Bob RobertCan I get this somehow? Any hint or suggestions welcome..
Keep DotNetting!! GeekFromIndia
modified on Wednesday, September 3, 2008 11:23 AM
-
I need to get a value from previous row in result with any extracted row...:confused:? here's with an example
Table structure
empid EmpName Otherdetails
1 Robert abcd
2 Peter abcd
1 Bob abcdAbove given is history table with records added for any update or delete...so Robert was the record inserted to main table and Record was updated as bob. So we have two records in history table as above with same id and modifieddate to get order Now here Robert updated his name as Bob....now in result set I need to get a list of employees and their previous name like..
Id NewName OldName
1 Robert Null
2 Peter Null
1 Bob RobertCan I get this somehow? Any hint or suggestions welcome..
Keep DotNetting!! GeekFromIndia
modified on Wednesday, September 3, 2008 11:23 AM
-
Show here your update command to see how you make that update.
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post.
This is actually a history table...which will have all insert, update and delete operations recorded...this works through a trigger... so for every update there will be a new row added to the table... So now I have old name and updated name as different rows...i need to get both names together in a query...can be selected using empId and modified date...
Keep DotNetting!! GeekFromIndia
-
I need to get a value from previous row in result with any extracted row...:confused:? here's with an example
Table structure
empid EmpName Otherdetails
1 Robert abcd
2 Peter abcd
1 Bob abcdAbove given is history table with records added for any update or delete...so Robert was the record inserted to main table and Record was updated as bob. So we have two records in history table as above with same id and modifieddate to get order Now here Robert updated his name as Bob....now in result set I need to get a list of employees and their previous name like..
Id NewName OldName
1 Robert Null
2 Peter Null
1 Bob RobertCan I get this somehow? Any hint or suggestions welcome..
Keep DotNetting!! GeekFromIndia
modified on Wednesday, September 3, 2008 11:23 AM
You can if you have say a datemodified column
select id, newname,oldname
from emp e1
left outer join emp e2 on e2.id = e1.id
and e2.datemodified = (select max(datemodified) from emp e3 where e3.id = e2.id
and e3.datemodified < e2.datemodified)I might have a bit wrong here, as I don't have sql server on this PC so can't test, but you get the idea - find the same id where the datemodified is the latest date for this id BUT less than the current record, using an out join as presumably not every record will have been changed. Hope this helps
Bob Ashfield Consultants Ltd
-
You can if you have say a datemodified column
select id, newname,oldname
from emp e1
left outer join emp e2 on e2.id = e1.id
and e2.datemodified = (select max(datemodified) from emp e3 where e3.id = e2.id
and e3.datemodified < e2.datemodified)I might have a bit wrong here, as I don't have sql server on this PC so can't test, but you get the idea - find the same id where the datemodified is the latest date for this id BUT less than the current record, using an out join as presumably not every record will have been changed. Hope this helps
Bob Ashfield Consultants Ltd
Well this query makes sense...but its giving all null for oldname...i guess something small we are missing here...
Keep DotNetting!! GeekFromIndia
-
You can if you have say a datemodified column
select id, newname,oldname
from emp e1
left outer join emp e2 on e2.id = e1.id
and e2.datemodified = (select max(datemodified) from emp e3 where e3.id = e2.id
and e3.datemodified < e2.datemodified)I might have a bit wrong here, as I don't have sql server on this PC so can't test, but you get the idea - find the same id where the datemodified is the latest date for this id BUT less than the current record, using an out join as presumably not every record will have been changed. Hope this helps
Bob Ashfield Consultants Ltd
got it...awesome... there was a small change though...last clause should be e3.datemodified < e1.datemodified
select id, newname,oldnamefrom emp e1left outer join emp e2 on e2.id = e1.idand e2.datemodified = (select max(datemodified) from emp e3 where e3.id = e2.idand e3.datemodified < e1.datemodified)
Thanks a lot...
Keep DotNetting!! GeekFromIndia
-
got it...awesome... there was a small change though...last clause should be e3.datemodified < e1.datemodified
select id, newname,oldnamefrom emp e1left outer join emp e2 on e2.id = e1.idand e2.datemodified = (select max(datemodified) from emp e3 where e3.id = e2.idand e3.datemodified < e1.datemodified)
Thanks a lot...
Keep DotNetting!! GeekFromIndia