Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Help on how to count guests in hotel every day Expand / Collapse
Author
Message
Posted Friday, January 25, 2013 3:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 08, 2013 9:59 AM
Points: 14, Visits: 59
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
Post #1411940
Posted Friday, January 25, 2013 3:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:13 AM
Points: 5,471, Visits: 23,510
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

Before posting a performance problem please read
Post #1411942
Posted Friday, January 25, 2013 4:39 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:53 PM
Points: 3,733, Visits: 7,072
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 )
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; They'll drag you down to their level and beat you with experience"
Post #1411953
Posted Saturday, January 26, 2013 11:33 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 12:30 AM
Points: 382, Visits: 344
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.
Post #1412106
Posted Sunday, January 27, 2013 8:17 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 6:03 PM
Points: 3,590, Visits: 5,098
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1412168
Posted Monday, January 28, 2013 11:46 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, March 27, 2014 5:29 AM
Points: 945, Visits: 1,760
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 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

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

Jeremy Oursler
Post #1412584
Posted Monday, January 28, 2013 12:39 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:47 AM
Points: 214, Visits: 722
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

Post #1412607
Posted Monday, January 28, 2013 6:55 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 6:03 PM
Points: 3,590, Visits: 5,098
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1412720
Posted Tuesday, January 29, 2013 6:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 08, 2013 9:59 AM
Points: 14, Visits: 59
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.
Post #1412996
Posted Tuesday, January 29, 2013 9:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 08, 2013 9:59 AM
Points: 14, Visits: 59
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.

Days Guests FemaleGuest MaleGuest


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]
Post #1413160
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse