need SQL Query which may require SELF Join

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

All Forums

General SQL Server Forums

New to SQL Server Programming

need SQL Query which may require SELF Join

Author

Topic

ebingeorge.sqlserver
Starting Member

7 Posts

Posted - 2011-07-17 : 16:03:32

There are two tables
Users
UserID GroupNumber ModifiedDate
1 AAA04 2011-06-04

UsersLog
UserID GroupNumber ModifiedDate
1 AAA01 2011-06-01
2 AAA02 2011-06-02
3 AAA03 2011-06-03

Note: Whenever an update occurs, the GroupNumber & ModifiedDate is inserted in Table UsersLog.

Example if AAA04 is Modified to AAA05 (Users Table on 5th June),
Then the two tables will have the following values

Users
UserID GroupNumber ModifiedDate
1 AAA05 2011-06-05

UsersLog
UserID GroupNumber ModifiedDate
1 AAA01 2011-06-01
2 AAA02 2011-06-02
3 AAA03 2011-06-03
4 AAA04 2011-06-04

I need a query, which will provide me with the following output:
UserID OldGroupNumber NewGroupNumber ModifiedDate
1 AAA01 AAA02 2011-06-02
1 AAA02 AAA03 2011-06-03
1 AAA03 AAA04 2011-06-04
1 AAA04 AAA05 2011-06-05

Is there a way to get the above data using a query (CTE/SELF Join).

Thanks. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources