Help with SQL query

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
rss2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
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
0
Patrick MatthewsCommented:
Another version, which seems to work OK for me.  Not pretty, but it does dispense with the temp table:

SELECT z1.ReservationID, z1.Username AS OrigUser, z2.Username AS NewUser, z2.DateAdded AS DateChanged
FROM
      (SELECT TOP 100 PERCENT 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
      FROM SomeTable t1
      ORDER BY t1.ReservationID, t1.DateAdded) z1 INNER JOIN
      (SELECT TOP 100 PERCENT t3.ReservationID, t3.Username, t3.DateAdded,
            (SELECT COUNT(t4.ReservationID)
            FROM SomeTable t4
            WHERE t3.ReservationID = t4.ReservationID AND t4.DateAdded <= t3.DateAdded) AS Num
      FROM SomeTable t3
      ORDER BY t3.ReservationID, t3.DateAdded) z2 ON z1.ReservationID = z2.ReservationID AND z1.Num = (z2.Num - 1)
ORDER BY z1.ReservationID, z1.Num
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
8080_DiverCommented:
So, this is a change log for your user ID's?  (It would have been a lot better ifyou had logged the ChangedFrom as well as the ChangedTo UserNames. ;-)
Essentially, you are going to have to identify the earliest entry for each ReservationID as a first step.  SQL_1 gives you a set of minimum dates by ID.  
 
Once that is found, you are go have to find the entry with the lowest date for each ReservationID that is not equal to the entry date of the first entry (found in the step above).   SQL_2 will do that for you.
Then you are going to have to repeat that until you don't have any more to find.
Now, probably the easiest answer would be to set up a stored procedure to accomplish your task.  That would let you create an in-memory, temporary table that matches your desired output.  In that table, I would have the following columns:
  1. ReservationID
  2. OriginalUserName
  3. OriginalEntryDate
  4. NewUserName
  5. ChangeDate
The first step in the Stored proedure (after creating the table) would be to prepare the data in the table, using SQL_3.  Then you can use SQL_4 (or something very close to it ;-) to update the rows with the information regarding changes.
Once that is accomplished, then all you have to do is use SQL_5 to select the rows for your report.  (Note, SQL_5 assumes that you do not want to have the rows where the current username has not been changed.)

SQL_1:
 
SELECT ReservationID, 
       Min(DateAdded)
FROM   YourTable
GROUP BY ReservationID;
 
 
 
SQL_2:
 
SELECT ReservationID, 
       Min(DateAdded) SecondDate
FROM
(
 SELECT ReservationID,
        DateAdded
 FROM   YourTable
 INNER JOIN
 (
  SELECT ReservationID, 
         Min(DateAdded) FirstDate
  FROM   YourTable;
 ) Y
 WHERE Z.FirstDate < DateAdded
) Z
GROUP BY ReservationID;
 
 
SQL_3:
 
INSERT INTO InterimTable
(
 ReservationID,
 OriginalUserName,
 OriginalEntryDate
)
SELECT ReservationID, 
       UserName
       DateAdded
FROM YourTable;
 
SQL_4:
 
UPDATE I
SET    I.NewUserName = D.NewUserName,
       I.ChangeDate  = D.SecondDate
FROM   InterimTable I
INNER JOIN
(
 SELECT Y.ReservationID, 
        Y.UserName AS NewUserName,
        C.SecondDate
 FROM   YourTable Y
 INNER JOIN 
 (
  SELECT ReservationID
         Min(DateAdded) SecondDate
  FROM   
  (
   SELECT ReservationID,
          DateAdded
   FROM   YourTable Y
   INNER JOIN
   (
    SELECT ReservationID, 
           Min(DateAdded) FirstDate
    FROM   YourTable
   ) A
   WHERE A.FirstDate < Y.DateAdded
  ) B
  GROUP BY B.ReservationID
 ) C
 ON C.ReservationID = Y.ReservationID
    C.SecondDate    = Y.DateAdded
 ) D 
 ON D.ReservationID = I.ReservationID
    D.SecondDate    = I.OriginalEntryDate;
;
 
 
SQL_5:
 
SELEC ReservationID
      OriginalUserName
      NewUserName 
      ChangeDate
FROM  InterimTable
ORDER BY ReservationID, ChangeDate;

Open in new window

0
Patrick MatthewsCommented:
rss2,

>>It's super duper urgent! :(

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

:)

Regards,

Patrick
0
rss2Author Commented:
Thank you!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.