nick-1043370 (1/6/2012)
HiI need to take a set of records for people with hotel check-in/check-out dates, and display a table that has a column for each date in the range, with a 1 if they need a room that night, or 0 if they don't.
e.g.
PersonID,Name,Checkin,Checkout
1,Fred,2012-01-02,2012-01-05
2,Bob,2012-01-01,2012-01-04
would produce the result:
PersonID,Name,Jan 1 2012,Jan 2 2012,Jan 3 2012,Jan 4 2012,Jan 5 2012
1,Fred,0,1,1,1,0
2,Bob,1,1,1,0,0
(people need a room on the night they check in, but not on the night they check out).
I'm not sure where to start! It looks like something that pivots are meant to address, but don't fully understand them yet, a stored proc or a table-value function. I'm inclined to think the table-value function would give me the most flexibility as I could join PersonID to other tables to get more data about the people if needed.
Any suggestions/similar sample code would be much appreciated.
Nick
Ok... I'm confused. People would almost always need the room for the night they checkin and never need a room for the night they checkout. Why would BOB not need his room for the last two nights and why would Fred not need his room on the day of Checkin (according to the data you posted).
Also, where are you getting THAT bit of information from?
--Jeff Moden
Change is inevitable... Change for the better is not.