July 29, 2009 at 5:31 pm
I feel dumb asking this because I know it's one of the oldest questions in Transact SQL but for the life of me I can't remember how to do this nor even what the correct term is so I can search or an article on it.
Using T-SQL, how do you find the NEXT record in a data set (a table in this case) where the NEXT record is the one with closes Primary Key value to the current record and that is for the same Group or set?
Because I can’t provide the DDL (due to NDA restrictions) I’ve created a sample or dummy table that mimics tracking of Hotel room rentals. Below is the DDL for it along with some sample data to populate it. I figured this was an easy to follow example that would provided the necessary sample data to answer this question. The table is called TRAFFIC and each time a guest at the hotel rents a room this table is populated with 1 row that record the room ID, the employee ID , the check in date, and the guests Last & First Name. The Check Out column is either NULL or contains the date the guest checked out or is supposed to check out.
Using this Hotel example how would I write a query that would return a history of a rooms history where each row of data includes:
1)The Room ID
2)The Prior Guests First & Last Name (for the first record for each room this would be NULL)
3)The Check IN & Check Out Date of the Prior Guest
4)The Name of the Current Guest
5)The Check In Date and Check Out date of the Current guest
For the Hotel Room with the ID value of 5, the output (using the sample data I’ve provided) would look like this:
5, NULL, NULL, NULL, NULL, Jane Smith
5, 2008/01/01, 2008/01/02, Jane Smith, 2008/01/03, 2008/01/06, Hurly Curly
5, 2008/01/03, 2008/01/06, Hurly Curly, 2008/01/07, NULL, Bahama Mama
Any help, even just a URL or reference to where I can find the answer to this would be most appreciated.
DDL
CREATE TABLE dbo.TRAFFIC( ID NUMERIC(18,0) NOT NULL,
hRoom NUMERIC(18,0) NOT NULL,
hEmployee NUMERIC(18,0) NOT NULL,
dtCheckIn DATETIME NULL,
dtCheckOut DATETIME NULL,
sGuestLName VARCHAR(50) NULL,
sGuestFName VARCHAR(50) NULL,
CONSTRAINT PK_TRAFFIC PRIMARY KEY CLUSTERED (ID ASC) ON [PRIMARY]
)
ON [PRIMARY]
/*SAMPLE DATA*/
INSERT INTO TRAFFIC(ID, hRoom, hEmployee, dtCheckIn, dtCheckOut, sGuestLName, sGuestFName)
VALUES (1 , 1, 8, '2008-01-01','2008-01-07', 'Doe', 'John')
INSERT INTO TRAFFIC(ID, hRoom, hEmployee, dtCheckIn, dtCheckOut, sGuestLName, sGuestFName)
VALUES (2 , 5, 8, '2008-01-01','2008-01-02', 'Smith ', 'Jane')
INSERT INTO TRAFFIC(ID, hRoom, hEmployee, dtCheckIn, dtCheckOut, sGuestLName, sGuestFName)
VALUES (3 , 5, 8, '2008-01-03','2008-01-06', 'Hurly', 'Curly')
INSERT INTO TRAFFIC(ID, hRoom, hEmployee, dtCheckIn, dtCheckOut, sGuestLName, sGuestFName)
VALUES (4 , 2, 7, '2008-01-03','2008-01-04', 'Johnson', 'The')
INSERT INTO TRAFFIC(ID, hRoom, hEmployee, dtCheckIn, dtCheckOut, sGuestLName, sGuestFName)
VALUES (5 , 3, 7, '2008-01-03','2008-01-04', 'Mouse', 'Mick D')
INSERT INTO TRAFFIC(ID, hRoom, hEmployee, dtCheckIn, dtCheckOut, sGuestLName, sGuestFName)
VALUES (6 , 4, 7, '2008-01-03', NULL, 'Pooh', 'W.D.')
INSERT INTO TRAFFIC(ID, hRoom, hEmployee, dtCheckIn, dtCheckOut, sGuestLName, sGuestFName)
VALUES (7 , 2, 8, '2008-01-04','2008-01-06', 'Wacka', 'Wucka')
INSERT INTO TRAFFIC(ID, hRoom, hEmployee, dtCheckIn, dtCheckOut, sGuestLName, sGuestFName)
VALUES (8 , 3, 8, '2008-01-05','2008-01-06', 'Duck', 'Howard')
INSERT INTO TRAFFIC(ID, hRoom, hEmployee, dtCheckIn, dtCheckOut, sGuestLName, sGuestFName)
VALUES (9 , 5, 7, '2008-01-07', NULL, 'Mama', 'Bahama')
INSERT INTO TRAFFIC(ID, hRoom, hEmployee, dtCheckIn, dtCheckOut, sGuestLName, sGuestFName)
VALUES (10, 6, 7, '2008-01-07', NULL, 'Ohh', 'Daddy')
INSERT INTO TRAFFIC(ID, hRoom, hEmployee, dtCheckIn, dtCheckOut, sGuestLName, sGuestFName)
VALUES (11, 1, 7, '2008-01-08','2008-01-09', 'Abdul', 'Pulla')
INSERT INTO TRAFFIC(ID, hRoom, hEmployee, dtCheckIn, dtCheckOut, sGuestLName, sGuestFName)
VALUES (12, 3, 7, '2008-01-08', NULL, 'EEhh', 'Wall')
Kindest Regards,
Just say No to Facebook!July 29, 2009 at 5:37 pm
Additonal INfo:
IN case it matters, my reason for this is so that I can compare the Chekc Out date for the Prior Guest to the Check In date of the Next Guest and find cases were the 2 do not match as well as were they do match and to also get a count of the difference in days when they don't match.
Kindest Regards,
Just say No to Facebook!July 29, 2009 at 9:39 pm
I would use a CTE with a Row_Number().
WITH Rooms AS (
SELECT
Row_Number() OVER (PARTITION BY HotelRoom.RoomID ORDER BY HotelRoom.RoomID, HotelRoom.CheckInDate) AS RowNum
, HotelRoom.RoomID
, HotelRoom.CheckInDate
, HotelRoom.CheckOutDate
, HotelRoom.CustFirstName
, HotelRoom.CustLastName
FROM HotelRoom
WHERE RoomID IS NOT Null
)
SELECT
PrevCust.RoomID
, PrevCust.CheckInDate, PrevCust.CheckOutDate, PrevCust.CustFirstName, CustLastName
, CurrCust.CheckInDate, CurrCust.CheckOutDate, CurrCust.CustFirstName, CustLastName
FROM Rooms AS PrevCust
INNER JOIN Rooms AS CurrCust
ON PrevCust.RoomID = CurrCust.RoomID
AND PrevCust.RowNum = CurrCust.RowNum - 1
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 16, 2009 at 8:55 am
Drew,
Thanks for replying and I know this is a few months old now (Just now getting time to go back and deal with this one) but unfortunately that will not work for my case. I was just told that this must be compatable with SQL Server 2000 and so use of CTE's is a no go.
I can;t believe your response was the only one as I know this "Prior" & "Next" record question is a classic one in T-SQL. I'm certain there are some great articles on this site about this I just was not able to find them in the first few searches and was hoping someone more familiar with this (perhaps even the author of one of these) would reply with a link to a piece on this and not necessarily with the actual answer.
Thanks again
Kindest Regards,
Just say No to Facebook!December 16, 2009 at 8:59 am
YSLGuru (12/16/2009)
Drew,Thanks for replying and I know this is a few months old now (Just now getting time to go back and deal with this one) but unfortunately that will not work for my case. I was just told that this must be compatable with SQL Server 2000 and so use of CTE's is a no go.
I can;t believe your response was the only one as I know this "Prior" & "Next" record question is a classic one in T-SQL. I'm certain there are some great articles on this site about this I just was not able to find them in the first few searches and was hoping someone more familiar with this (perhaps even the author of one of these) would reply with a link to a piece on this and not necessarily with the actual answer.
Thanks again
Take the CTE and convert it to a derived table in your FROM clause.
EDIT: Didn't read the ENTIRE CTE. You will also need to figure out a replacement for the ROW_NUMBER() function since this also needs to work in SQL Server 2000.
Also, this is the first time I have seen this post. 😉
December 16, 2009 at 9:11 am
Lynn Pettis (12/16/2009)
YSLGuru (12/16/2009)
Drew,Thanks for replying and I know this is a few months old now (Just now getting time to go back and deal with this one) but unfortunately that will not work for my case. I was just told that this must be compatable with SQL Server 2000 and so use of CTE's is a no go.
I can;t believe your response was the only one as I know this "Prior" & "Next" record question is a classic one in T-SQL. I'm certain there are some great articles on this site about this I just was not able to find them in the first few searches and was hoping someone more familiar with this (perhaps even the author of one of these) would reply with a link to a piece on this and not necessarily with the actual answer.
Thanks again
Take the CTE and convert it to a derived table in your FROM clause.
EDIT: Didn't read the ENTIRE CTE. You will also need to figure out a replacement for the ROW_NUMBER() function since this also needs to work in SQL Server 2000.
Also, this is the first time I have seen this post. 😉
Thanks Lynn. i was suprised you had not responded (along with Jeff, Steve and a few others) as you folks usually are the first to reply to most T-SQL postings. Betwene my last reply and this oneI founf Jeff Modems below piece which also addresses this and it looks to be something from a collaboration of many including your self.
Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)
http://www.sqlservercentral.com/articles/T-SQL/68467/
Kindest Regards,
Just say No to Facebook!December 16, 2009 at 9:15 am
Looked back at when this was posted. That was right around the time my oldest daughter left for Basic Training for the Army. I may have just overlooked the post and it just got lost with the lack of responses.
December 16, 2009 at 9:44 am
Lynn,
Looks like I still need help after all. The article by Jeff Modem is I think over kill for what I am looking for. I'm not doing any kind of update, just SELECTS and am not dealing with a very large data set but I still want to avoid non-set based approaches like a curosr.
That being said there has to be a generic, T-SQL set based solution for retruning data from the PRIOR or the NEXT row so that you can calculate a value based on the value of one or more fields in the current row and the same fields in the PRIOR or the NEXT row like calculating how many days a hotel room is vacant somewhat like this psudeo code which I know is not syntax correct :
SELECT DateDiff(d, GP.dtCheckOut, G.dtCheckIn) AS 'iDaysVacant'
FROM GUESTS G /*Current Guest*/ Left Outer Join GUESTS GP /*Prior Guest*/ ON G.hRoom = GP.hRoom
AND GP.dtCheckOut <= G.dtCheckIn
WHERE 1 = 1
I know there's a way to do this in pre-SQL 2005 and without using a temp table or some other in between object but for the life of me I can't recall.
Kindest Regards,
Just say No to Facebook!December 16, 2009 at 9:47 am
I may not have much time during the day, we are finally getting some of our new servers for a PeopleSoft Tools/App upgrade. It may be tonight before I can look at this.
Edit: I just emailed this topic to my home email. that should hopefully remind me to check on it.
December 16, 2009 at 9:50 am
Thanks Lynn. If I happen to stumble across the answer before then I'll post back again.
Kindest Regards,
Just say No to Facebook!December 16, 2009 at 10:18 am
Here's one way to do it - I suspect the performance would really suck on a large data set though.
SELECT pt.dtCheckIn, pt.dtCheckOut, pt.sGuestFName, pt.sGuestLName, ct.*
FROM (
SELECT CurrentID = t.ID,
PriorID = (SELECT MAX(ID) FROM #TRAFFIC WHERE ID < t.ID AND hRoom = t.hRoom)
FROM #TRAFFIC t
) d
INNER JOIN #TRAFFIC ct ON ct.ID = d.CurrentID
LEFT JOIN #TRAFFIC pt ON pt.ID = d.PriorID
ORDER BY ct.hRoom
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply