We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Help with SQL query

Medium Priority
202 Views
Last Modified: 2012-05-07
Hi Gurus,

I have a table which lists a ReservationID, UserName, DateAdded.

The rows in the table look like this:
ReservationID         Username        DateAdded
1                             COLOGNE        30/06/2009
1                             HAWK              01/07/2009
1                             GILES               02/07/2009
2                             SMITH              30/06/2009
2                             JULES              02/07/2009
3                             COLOGNE       30/06/2009
3                             HEATHER        01/07/2009
3                            COLOGNE        02/07/2009

I need a report on this table that looks like this:
ReservationID            Original UserName     New UserName        Date Changed
1                                COLOGNE                  HAWK                       01/07/2009
1                                HAWK                       GILES                         02/07/2009
2                                SMITH                        JULES                        02/07/2009
3                                COLOGNE                 HEATHER                   01/07/2009
3                                HEATHER                  COLOGNE                  02/07/2009

How do I do this??

It's super duper urgent! :(

Thank you!!!
rss2
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010

Commented:
The following seems to be working for me:

SELECT t1.ReservationID, t1.Username, t1.DateAdded,
      (SELECT COUNT(t2.ReservationID)
      FROM SomeTable t2
      WHERE t1.ReservationID = t2.ReservationID AND t2.DateAdded <= t1.DateAdded) AS Num
INTO #tmp
FROM SomeTable t1
ORDER BY t1.ReservationID, t1.DateAdded

SELECT t1.ReservationID, t1.Username AS OrigUser, t2.Username AS NewUser, t2.DateAdded AS DateChanged
FROM #tmp t1 INNER JOIN
      #tmp t2 ON t1.ReservationID = t2.ReservationID AND t1.Num = (t2.Num - 1)
ORDER BY t1.ReservationID, t1.Num

DROP TABLE #tmp
CERTIFIED EXPERT
Top Expert 2010
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT
Top Expert 2010

Commented:
rss2,

>>It's super duper urgent! :(

In that case, have you had a chance to test my suggestions?

:)

Regards,

Patrick

Author

Commented:
Thank you!!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.