Home Forums SQL Server 2008 T-SQL (SS2K8) Returning a column per date in a range RE: Returning a column per date in a range

  • January 6, 2012 at 6:11 am

    #1429578

    nick-1043370 (1/6/2012)

    Hi

    I 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

    Can you provide some more detail about the date range, Nick?

    Will it always be a single month, starting at the 1st? = simple

    Random number of days, starting at random DOM = harder.

    Cheers

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]