Solved

Help with SQL query

Posted on 2009-07-05
5
158 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
0
Comment
Question by:rss2
  • 3
5 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24781345
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
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 250 total points
ID: 24781454
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
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 250 total points
ID: 24781511
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24790792
rss2,

>>It's super duper urgent! :(

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

:)

Regards,

Patrick
0
 

Author Closing Comment

by:rss2
ID: 31599953
Thank you!!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now