A simplified version of my data structure is as follows:
Room
Field Name Type UniqueID UniqueIdentifier Room Name Text
Room Booking
Field Name Type UniqueID UniqueIdentifier BookedDate Date CheckedIn Yes/No CheckedOut Yes/No RoomID UniqueIdentifier
What I need to retrieve is the UniqueID of the most recent room booking for each room that has been checked in to.
My query is as follows.
select R.UniqueID as RoomID, (Select top 1 UniqueID from RoomBooking as RB where RB.RoomID = R.UniqueID and CheckedIn=true order by BookedDate DESC, UniqueID Desc) as MostRecentID
From Room R
If found the subquery instructions here, and adding the tip to also sort by the UniqueID in the subquery solved other issues I was having.
http://allenbrowne.com/subquery-01.html
The problem is that my MostRecentID always comes back null. I know I have records that meet the criteria because if I change the top 1 to a count(*) and remove the Order By clause, the query returns results.
Where am I going wrong? How can I rewrite this statement?