Help on how to count guests in hotel every day

Question

Help on how to count guests in hotel every day

Maple07-598122

SSC Enthusiast

Points: 156
More actions
January 25, 2013 at 3:02 pm

#288457

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

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

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]

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 2

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

db4breakfast Ten Centuries Points: 1097 More actions · Answer 3

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.

Dwain Camps SSC Guru Points: 86948 More actions · Answer 4

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

CapnHector SSCoach Points: 16143 More actions · Answer 5

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]

below86 SSChampion Points: 11532 More actions · Answer 6

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.

Dwain Camps SSC Guru Points: 86948 More actions · Answer 7

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

Maple07-598122 SSC Enthusiast Points: 156 More actions · Answer 8

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.

Maple07-598122 SSC Enthusiast Points: 156 More actions · Answer 9

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]

Dwain Camps SSC Guru Points: 86948 More actions · Answer 10

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

srienstr SSCrazy Points: 2435 More actions · Answer 11

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.

Maple07-598122 SSC Enthusiast Points: 156 More actions · Answer 12

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

Davio Old Hand Points: 343 More actions · Answer 13

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.

Dwain Camps SSC Guru Points: 86948 More actions · Answer 14

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