Help on how to count guests in hotel every day

  • I have a table keeps guest records in a hotel. For each guest I have check in date and check out date. If I want to know how many guests the hotel served on each day in December 2012, how should I write the query?

    Thanks

  • Maple07-598122 (1/25/2013)


    I have a table keeps guest records in a hotel. For each guest I have check in date and check out date. If I want to know how many guests the hotel served on each day in December 2012, how should I write the query?

    Thanks

    without only that much information ... a working answer is to say the least - difficult.

    If you post your table definition, some sample data, someone will probably post the necessary tested T-SQL.

    To assist those who desire to assist you, please read the first link in my signature block,

    it gives examples of data required and contains the T-SQL code to allow you to do this rapidly and easily.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • As BitBucket has stated, it's a challenge to assume what you want without the basics needed for everyone to give it a whirl. Not sure if the example below is what you were intending, but maybe it can help point you in the right direction (or not :-D)CREATE TABLE #Guests (checkin date, checkout date, FullName varchar(50))

    INSERT INTO #Guests

    SELECT '10/12/2012', '10/16/2012', 'Corky Doe' U-NION ALL

    SELECT '12/12/2012', '12/17/2012', 'Janice Doe' U-NION ALL

    SELECT '11/12/2012', '11/24/2012', 'Howard Stern' U-NION ALL

    SELECT '12/12/2012', '12/13/2012', 'Abagail Johnson' U-NION ALL

    SELECT '12/12/2012', '11/15/2012', 'Teddy Sanft' U-NION ALL

    SELECT '12/12/2012', '11/18/2012', 'John Overton' U-NION ALL

    SELECT '12/12/2012', '12/19/2012', 'Sally Jenson' U-NION ALL

    SELECT '12/12/2012', '10/20/2012', 'Tiffany Blue'

    ; WITH Served (NumOfDays, ByMonth) AS

    (

    SELECT COUNT(FullName), DATEPART(month, Checkin) [Month] FROM #Guests

    GROUP BY DATEPART(month, Checkin)

    )

    SELECT NumOfDays, ByMonth FROM Served

    WHERE ByMOnth = 12

    D-ROP TABLE #Guests

    Note :: The hyphens are added to some of the SQL keywords to avoid setting off my proxy server...

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • Hi,

    Here's one of the possible solution.

    -- # of guests the hotel served on each day in December 2012

    SELECT g.checkin AS [date], COUNT(*) AS [#of_guests_served]

    FROM dbo.guests g

    WHERE g.checkin BETWEEN CAST('20120101' AS datetime) AND CAST('20121231' AS datetime)

    GROUP BY g.checkin;

    --------------------------------------------------------------------------------------
    Hai Ton
    My Db4Breakfast blog.

  • I think you need a calendar table to do this:

    CREATE TABLE #Guests (checkin date, checkout date, FullName varchar(50))

    INSERT INTO #Guests

    SELECT '10/12/2012', '10/16/2012', 'Corky Doe' UNION ALL

    SELECT '12/12/2012', '12/17/2012', 'Janice Doe' UNION ALL

    SELECT '11/12/2012', '11/24/2012', 'Howard Stern' UNION ALL

    SELECT '12/12/2012', '12/13/2012', 'Abagail Johnson' UNION ALL

    SELECT '12/12/2012', '12/15/2012', 'Teddy Sanft' UNION ALL

    SELECT '12/12/2012', '12/18/2012', 'John Overton' UNION ALL

    SELECT '12/12/2012', '12/19/2012', 'Sally Jenson' UNION ALL

    SELECT '10/20/2012', '12/12/2012', 'Tiffany Blue'

    DECLARE @StartOfMonth DATE = '2012-12-01';

    WITH Calendar AS (

    SELECT TOP (DAY(DATEADD(mm,DATEDIFF(mm,-1,@StartOfMonth),-1)))

    [Day]=DATEADD(day, number - 1, @StartOfMonth)

    FROM [master].dbo.spt_values Tally

    WHERE [Type] = 'P' AND Number BETWEEN 1 AND 31)

    SELECT [Day], Guests=ISNULL(COUNT(FullName), 0)

    FROM Calendar

    LEFT JOIN #Guests ON [Day] >= checkin AND [Day] < checkout

    GROUP BY [Day]

    DROP TABLE #Guests


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (1/27/2013)


    I think you need a calendar table to do this:

    CREATE TABLE #Guests (checkin date, checkout date, FullName varchar(50))

    INSERT INTO #Guests

    SELECT '10/12/2012', '10/16/2012', 'Corky Doe' UNION ALL

    SELECT '12/12/2012', '12/17/2012', 'Janice Doe' UNION ALL

    SELECT '11/12/2012', '11/24/2012', 'Howard Stern' UNION ALL

    SELECT '12/12/2012', '12/13/2012', 'Abagail Johnson' UNION ALL

    SELECT '12/12/2012', '12/15/2012', 'Teddy Sanft' UNION ALL

    SELECT '12/12/2012', '12/18/2012', 'John Overton' UNION ALL

    SELECT '12/12/2012', '12/19/2012', 'Sally Jenson' UNION ALL

    SELECT '10/20/2012', '12/12/2012', 'Tiffany Blue'

    DECLARE @StartOfMonth DATE = '2012-12-01';

    WITH Calendar AS (

    SELECT TOP (DAY(DATEADD(mm,DATEDIFF(mm,-1,@StartOfMonth),-1)))

    [Day]=DATEADD(day, number - 1, @StartOfMonth)

    FROM [master].dbo.spt_values Tally

    WHERE [Type] = 'P' AND Number BETWEEN 1 AND 31)

    SELECT [Day], Guests=ISNULL(COUNT(FullName), 0)

    FROM Calendar

    LEFT JOIN #Guests ON [Day] >= checkin AND [Day] < checkout

    GROUP BY [Day]

    DROP TABLE #Guests

    The only way to not use a calendar table is to have only a single date you want the count for. for the whole month you need the calendar table from my keyboard mashing.


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • I've made assumptions that you may have more than one guest per room. I also assume that if you have one guest checking out today and one checking in that you want a count of 2, that would be the number serviced that day. If not that would be an easy change to the 'between' statement below. It's not as simple as the other code listed before but it doesn't rely on system tables and the dates not hard coded. This assumes you want the data for the prior month, if ran in January 2013 it pulls all data for December 2012.

    CREATE TABLE #Guests (

    checkin datetime,

    checkout datetime,

    FullName varchar(50),

    number_of_guest int

    )

    ;

    INSERT INTO #Guests

    SELECT '10/12/2012', '10/16/2012', 'Corky Doe', 1 UNION ALL

    SELECT '12/12/2012', '12/17/2012', 'Janice Doe', 2 UNION ALL

    SELECT '11/12/2012', '11/24/2012', 'Howard Stern', 4 UNION ALL

    SELECT '12/12/2012', '12/13/2012', 'Abagail Johnson', 1 UNION ALL

    SELECT '12/12/2012', '12/15/2012', 'Teddy Sanft', 6 UNION ALL

    SELECT '12/12/2012', '12/18/2012', 'John Overton', 2 UNION ALL

    SELECT '12/12/2012', '12/19/2012', 'Sally Jenson', 2 UNION ALL

    SELECT '10/20/2012', '12/12/2012', 'Tiffany Blue', 1

    declare @days_of_month as datetime;

    declare @month_end as datetime;

    declare @month_begin as datetime;

    set @month_end = (select (DATEADD ( dd, -(DATEPART ( dd, GETDATE())), convert(char(10),GETDATE(),101))));

    set @month_begin = (select (DATEADD ( dd, -(DATEPART ( dd, @month_end)) + 1, convert(char(10), @month_end,101))));

    --select @month_end as month_end, @month_begin as month_begin

    set @days_of_month = @month_begin;

    -- drop table #guest_by_day

    CREATE TABLE #guest_by_day (

    days_of_month datetime,

    guest_served int

    )

    ;

    LOOPHere:

    insert into #guest_by_day

    select @days_of_month as days_of_month, 0 as guest_served

    ;

    set @days_of_month = @days_of_month + 1;

    If @days_of_month <= @month_end goto LOOPHere

    select r.days_of_month,

    sum(number_of_guest) as guest

    --count(*) as guest

    into #temp

    from #guests l

    inner join #guest_by_day r on

    r.days_of_month between l.checkin and l.checkout

    group by r.days_of_month

    select days_of_month, sum(guest) as guest

    into #temp2

    from #temp

    group by days_of_month

    update #guest_by_day

    set guest_served = guest

    from #guest_by_day l

    inner join #temp2 r on

    l.days_of_month = r.days_of_month

    select *

    from #guest_by_day

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

  • CapnHector (1/28/2013)


    dwain.c (1/27/2013)


    I think you need a calendar table to do this:

    CREATE TABLE #Guests (checkin date, checkout date, FullName varchar(50))

    INSERT INTO #Guests

    SELECT '10/12/2012', '10/16/2012', 'Corky Doe' UNION ALL

    SELECT '12/12/2012', '12/17/2012', 'Janice Doe' UNION ALL

    SELECT '11/12/2012', '11/24/2012', 'Howard Stern' UNION ALL

    SELECT '12/12/2012', '12/13/2012', 'Abagail Johnson' UNION ALL

    SELECT '12/12/2012', '12/15/2012', 'Teddy Sanft' UNION ALL

    SELECT '12/12/2012', '12/18/2012', 'John Overton' UNION ALL

    SELECT '12/12/2012', '12/19/2012', 'Sally Jenson' UNION ALL

    SELECT '10/20/2012', '12/12/2012', 'Tiffany Blue'

    DECLARE @StartOfMonth DATE = '2012-12-01';

    WITH Calendar AS (

    SELECT TOP (DAY(DATEADD(mm,DATEDIFF(mm,-1,@StartOfMonth),-1)))

    [Day]=DATEADD(day, number - 1, @StartOfMonth)

    FROM [master].dbo.spt_values Tally

    WHERE [Type] = 'P' AND Number BETWEEN 1 AND 31)

    SELECT [Day], Guests=ISNULL(COUNT(FullName), 0)

    FROM Calendar

    LEFT JOIN #Guests ON [Day] >= checkin AND [Day] < checkout

    GROUP BY [Day]

    DROP TABLE #Guests

    The only way to not use a calendar table is to have only a single date you want the count for. for the whole month you need the calendar table from my keyboard mashing.

    I forgot to mention that the solution I posted doesn't count guests on the check out date (but that can easily be changed).


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • I am sorry for not providing detail information, I was on a rush on Friday. But just as all of you assumed, there are CustomerID, checkin (Date), checkout(date), and roomNumber. I am reading posts now. And appreciate all your help. Thanks.

  • This is what I want and it is really smart. One more help needed here, if in the table I have one more column Gender, can I get something like below in one query. Thank you.

    DaysGuests FemaleGuestMaleGuest

    CREATE TABLE #Guests (checkin date, checkout date, FullName varchar(50), Gender char(1))

    INSERT INTO #Guests

    SELECT '10/12/2012', '10/16/2012', 'Corky Doe', 'F' UNION ALL

    SELECT '12/12/2012', '12/17/2012', 'Janice Doe', 'F' UNION ALL

    SELECT '11/12/2012', '11/24/2012', 'Howard Stern', 'M' UNION ALL

    SELECT '12/12/2012', '12/13/2012', 'Abagail Johnson', 'F' UNION ALL

    SELECT '12/12/2012', '12/15/2012', 'Teddy Sanft', 'M' UNION ALL

    SELECT '12/12/2012', '12/18/2012', 'John Overton', 'M' UNION ALL

    SELECT '12/12/2012', '12/19/2012', 'Sally Jenson', 'M' UNION ALL

    SELECT '10/20/2012', '12/12/2012', 'Tiffany Blue', 'F'

    DECLARE @StartOfMonth DATE = '2012-12-01';

    WITH Calendar AS (

    SELECT TOP (DAY(DATEADD(mm,DATEDIFF(mm,-1,@StartOfMonth),-1)))

    [Day]=DATEADD(day, number - 1, @StartOfMonth)

    FROM [master].dbo.spt_values Tally

    WHERE [Type] = 'P' AND Number BETWEEN 1 AND 31)

    SELECT [Day], Guests=ISNULL(COUNT(FullName), 0)

    FROM Calendar

    LEFT JOIN #Guests ON [Day] >= checkin AND [Day] <= checkout

    GROUP BY [Day]

  • You can probably make a change something like this:

    SELECT [Day], MaleGuests=ISNULL(SUM(CASE Gender WHEN 'M' THEN 1 END), 0)

    , FemaleGuests=ISNULL(SUM(CASE Gender WHEN 'F' THEN 1 END), 0)


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • I would ordinarily add an "Else 0" prior to the End in those case statements rather than the isnull function. Is there a performance difference, or just personal preference?


    Puto me cogitare, ergo puto me esse.
    I think that I think, therefore I think that I am.

  • I couldn’t get my assignment done without your help. Thank you.

  • This is very nearly the same type of problem discussed in Phil Factor's SQL Speed Phreak challenge http://ask.sqlservercentral.com/questions/1227/the-subscription-list-sql-problem.html, which is also discussed nicely here http://www.simple-talk.com/sql/performance/writing-efficient-sql-set-based-speed-phreakery/. Besides being a good read, I think that you could use the winning code easily enough, substituting days for months and guests for subscribers.

  • srienstr (1/30/2013)


    I would ordinarily add an "Else 0" prior to the End in those case statements rather than the isnull function. Is there a performance difference, or just personal preference?

    Actually, you need to use the ISNULL because there may be days when there are no guests.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply