InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 11:00:17 GMT20http://www.sqlservercentral.com/Forums/Topic1411940-392-1.aspx[quote][b]srienstr (1/31/2013)[/b][hr]I get the same results both ways, even for days with no guests of one gender or no guests at all.[code]CREATE TABLE #Guests (checkin date, checkout date, FullName varchar(50), Gender char(1))INSERT INTO #GuestsSELECT '10/12/2012', '10/16/2012', 'Corky Doe','M' UNION ALLSELECT '12/12/2012', '12/17/2012', 'Janice Doe','F' UNION ALLSELECT '11/12/2012', '11/24/2012', 'Howard Stern','M' UNION ALLSELECT '12/12/2012', '12/13/2012', 'Abagail Johnson','F' UNION ALLSELECT '12/12/2012', '12/15/2012', 'Teddy Sanft','M' UNION ALLSELECT '12/12/2012', '12/18/2012', 'John Overton','M' UNION ALLSELECT '12/12/2012', '12/19/2012', 'Sally Jenson','F' UNION ALLSELECT '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), MaleGuests=ISNULL(SUM(CASE Gender WHEN 'M' THEN 1 END), 0), FemaleGuests=ISNULL(SUM(CASE Gender WHEN 'F' THEN 1 END), 0), Guests2=COUNT(FullName), MaleGuests2=SUM(CASE Gender WHEN 'M' THEN 1 else 0 END), FemaleGuests2=SUM(CASE Gender WHEN 'F' THEN 1 else 0 END)FROM CalendarLEFT JOIN #Guests ON [Day] >= checkin AND [Day] < checkoutGROUP BY [Day]DROP TABLE #Guests[/code][/quote]Sorry. I think I gave you a bad answer without thinking to much about it.I believe the ISNULL was just a carryover from my original solution where it was needed.Thu, 31 Jan 2013 18:14:25 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1411940-392-1.aspxI get the same results both ways, even for days with no guests of one gender or no guests at all.[code]CREATE TABLE #Guests (checkin date, checkout date, FullName varchar(50), Gender char(1))INSERT INTO #GuestsSELECT '10/12/2012', '10/16/2012', 'Corky Doe','M' UNION ALLSELECT '12/12/2012', '12/17/2012', 'Janice Doe','F' UNION ALLSELECT '11/12/2012', '11/24/2012', 'Howard Stern','M' UNION ALLSELECT '12/12/2012', '12/13/2012', 'Abagail Johnson','F' UNION ALLSELECT '12/12/2012', '12/15/2012', 'Teddy Sanft','M' UNION ALLSELECT '12/12/2012', '12/18/2012', 'John Overton','M' UNION ALLSELECT '12/12/2012', '12/19/2012', 'Sally Jenson','F' UNION ALLSELECT '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), MaleGuests=ISNULL(SUM(CASE Gender WHEN 'M' THEN 1 END), 0), FemaleGuests=ISNULL(SUM(CASE Gender WHEN 'F' THEN 1 END), 0), Guests2=COUNT(FullName), MaleGuests2=SUM(CASE Gender WHEN 'M' THEN 1 else 0 END), FemaleGuests2=SUM(CASE Gender WHEN 'F' THEN 1 else 0 END)FROM CalendarLEFT JOIN #Guests ON [Day] >= checkin AND [Day] < checkoutGROUP BY [Day]DROP TABLE #Guests[/code]Thu, 31 Jan 2013 10:11:09 GMTsrienstrhttp://www.sqlservercentral.com/Forums/Topic1411940-392-1.aspx[quote][b]murgatroid (1/30/2013)[/b][hr]This is very nearly the same type of problem discussed in Phil Factor's SQL Speed Phreak challenge [url]http://ask.sqlservercentral.com/questions/1227/the-subscription-list-sql-problem.html[/url], which is also discussed nicely here [url]http://www.simple-talk.com/sql/performance/writing-efficient-sql-set-based-speed-phreakery/[/url]. Besides being a good read, I think that you could use the winning code easily enough, substituting days for months and guests for subscribers.[/quote]Heavens to murgatroid!I'd seen that article and forgotten about it. Thanks for reminding me.Wed, 30 Jan 2013 17:56:00 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1411940-392-1.aspx[quote][b]srienstr (1/30/2013)[/b][hr]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?[/quote]Actually, you need to use the ISNULL because there may be days when there are no guests.Wed, 30 Jan 2013 17:54:12 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1411940-392-1.aspxThis is very nearly the same type of problem discussed in Phil Factor's SQL Speed Phreak challenge [url]http://ask.sqlservercentral.com/questions/1227/the-subscription-list-sql-problem.html[/url], which is also discussed nicely here [url]http://www.simple-talk.com/sql/performance/writing-efficient-sql-set-based-speed-phreakery/[/url]. Besides being a good read, I think that you could use the winning code easily enough, substituting days for months and guests for subscribers.Wed, 30 Jan 2013 14:39:55 GMTDaviohttp://www.sqlservercentral.com/Forums/Topic1411940-392-1.aspxI couldn’t get my assignment done without your help. Thank you.Wed, 30 Jan 2013 10:45:00 GMTMaple07-598122http://www.sqlservercentral.com/Forums/Topic1411940-392-1.aspxI 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?Wed, 30 Jan 2013 09:41:32 GMTsrienstrhttp://www.sqlservercentral.com/Forums/Topic1411940-392-1.aspxYou can probably make a change something like this:[code="sql"]SELECT [Day], MaleGuests=ISNULL(SUM(CASE Gender WHEN 'M' THEN 1 END), 0) , FemaleGuests=ISNULL(SUM(CASE Gender WHEN 'F' THEN 1 END), 0)[/code]Tue, 29 Jan 2013 17:26:27 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1411940-392-1.aspxThis 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 MaleGuestCREATE TABLE #Guests (checkin date, checkout date, FullName varchar(50), Gender char(1))INSERT INTO #GuestsSELECT '10/12/2012', '10/16/2012', 'Corky Doe', 'F' UNION ALLSELECT '12/12/2012', '12/17/2012', 'Janice Doe', 'F' UNION ALLSELECT '11/12/2012', '11/24/2012', 'Howard Stern', 'M' UNION ALLSELECT '12/12/2012', '12/13/2012', 'Abagail Johnson', 'F' UNION ALLSELECT '12/12/2012', '12/15/2012', 'Teddy Sanft', 'M' UNION ALLSELECT '12/12/2012', '12/18/2012', 'John Overton', 'M' UNION ALLSELECT '12/12/2012', '12/19/2012', 'Sally Jenson', 'M' UNION ALLSELECT '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 CalendarLEFT JOIN #Guests ON [Day] >= checkin AND [Day] <= checkoutGROUP BY [Day]Tue, 29 Jan 2013 09:50:48 GMTMaple07-598122http://www.sqlservercentral.com/Forums/Topic1411940-392-1.aspxI 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.Tue, 29 Jan 2013 06:30:47 GMTMaple07-598122http://www.sqlservercentral.com/Forums/Topic1411940-392-1.aspx[quote][b]CapnHector (1/28/2013)[/b][hr][quote][b]dwain.c (1/27/2013)[/b][hr]I think you need a calendar table to do this:[code="sql"]CREATE TABLE #Guests (checkin date, checkout date, FullName varchar(50))INSERT INTO #GuestsSELECT '10/12/2012', '10/16/2012', 'Corky Doe' UNION ALLSELECT '12/12/2012', '12/17/2012', 'Janice Doe' UNION ALLSELECT '11/12/2012', '11/24/2012', 'Howard Stern' UNION ALLSELECT '12/12/2012', '12/13/2012', 'Abagail Johnson' UNION ALLSELECT '12/12/2012', '12/15/2012', 'Teddy Sanft' UNION ALLSELECT '12/12/2012', '12/18/2012', 'John Overton' UNION ALLSELECT '12/12/2012', '12/19/2012', 'Sally Jenson' UNION ALLSELECT '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 CalendarLEFT JOIN #Guests ON [Day] >= checkin AND [Day] < checkoutGROUP BY [Day]DROP TABLE #Guests[/code][/quote]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.[/quote]I forgot to mention that the solution I posted doesn't count guests on the check out date (but that can easily be changed).Mon, 28 Jan 2013 18:55:50 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1411940-392-1.aspxI'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 #GuestsSELECT '10/12/2012', '10/16/2012', 'Corky Doe', 1 UNION ALLSELECT '12/12/2012', '12/17/2012', 'Janice Doe', 2 UNION ALLSELECT '11/12/2012', '11/24/2012', 'Howard Stern', 4 UNION ALLSELECT '12/12/2012', '12/13/2012', 'Abagail Johnson', 1 UNION ALLSELECT '12/12/2012', '12/15/2012', 'Teddy Sanft', 6 UNION ALLSELECT '12/12/2012', '12/18/2012', 'John Overton', 2 UNION ALLSELECT '12/12/2012', '12/19/2012', 'Sally Jenson', 2 UNION ALLSELECT '10/20/2012', '12/12/2012', 'Tiffany Blue', 1declare @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_beginset @days_of_month = @month_begin;-- drop table #guest_by_dayCREATE TABLE #guest_by_day ( days_of_month datetime, guest_served int);LOOPHere:insert into #guest_by_dayselect @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 LOOPHereselect r.days_of_month, sum(number_of_guest) as guest-- count(*) as guestinto #tempfrom #guests l inner join #guest_by_day r on r.days_of_month between l.checkin and l.checkoutgroup by r.days_of_monthselect days_of_month, sum(guest) as guestinto #temp2from #tempgroup by days_of_month update #guest_by_dayset guest_served = guestfrom #guest_by_day l inner join #temp2 r on l.days_of_month = r.days_of_monthselect *from #guest_by_dayMon, 28 Jan 2013 12:39:23 GMTbeloweryhttp://www.sqlservercentral.com/Forums/Topic1411940-392-1.aspx[quote][b]dwain.c (1/27/2013)[/b][hr]I think you need a calendar table to do this:[code="sql"]CREATE TABLE #Guests (checkin date, checkout date, FullName varchar(50))INSERT INTO #GuestsSELECT '10/12/2012', '10/16/2012', 'Corky Doe' UNION ALLSELECT '12/12/2012', '12/17/2012', 'Janice Doe' UNION ALLSELECT '11/12/2012', '11/24/2012', 'Howard Stern' UNION ALLSELECT '12/12/2012', '12/13/2012', 'Abagail Johnson' UNION ALLSELECT '12/12/2012', '12/15/2012', 'Teddy Sanft' UNION ALLSELECT '12/12/2012', '12/18/2012', 'John Overton' UNION ALLSELECT '12/12/2012', '12/19/2012', 'Sally Jenson' UNION ALLSELECT '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 CalendarLEFT JOIN #Guests ON [Day] >= checkin AND [Day] < checkoutGROUP BY [Day]DROP TABLE #Guests[/code][/quote]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.Mon, 28 Jan 2013 11:46:36 GMTCapnHectorhttp://www.sqlservercentral.com/Forums/Topic1411940-392-1.aspxI think you need a calendar table to do this:[code="sql"]CREATE TABLE #Guests (checkin date, checkout date, FullName varchar(50))INSERT INTO #GuestsSELECT '10/12/2012', '10/16/2012', 'Corky Doe' UNION ALLSELECT '12/12/2012', '12/17/2012', 'Janice Doe' UNION ALLSELECT '11/12/2012', '11/24/2012', 'Howard Stern' UNION ALLSELECT '12/12/2012', '12/13/2012', 'Abagail Johnson' UNION ALLSELECT '12/12/2012', '12/15/2012', 'Teddy Sanft' UNION ALLSELECT '12/12/2012', '12/18/2012', 'John Overton' UNION ALLSELECT '12/12/2012', '12/19/2012', 'Sally Jenson' UNION ALLSELECT '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 CalendarLEFT JOIN #Guests ON [Day] >= checkin AND [Day] < checkoutGROUP BY [Day]DROP TABLE #Guests[/code]Sun, 27 Jan 2013 20:17:33 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1411940-392-1.aspxHi,Here's one of the possible solution.-- # of guests the hotel served on each day in December 2012SELECT g.checkin AS [date], COUNT(*) AS [#of_guests_served]FROM dbo.guests gWHERE g.checkin BETWEEN CAST('20120101' AS datetime) AND CAST('20121231' AS datetime)GROUP BY g.checkin;Sat, 26 Jan 2013 23:33:24 GMTdb4breakfasthttp://www.sqlservercentral.com/Forums/Topic1411940-392-1.aspxAs 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)[code="sql"]CREATE TABLE #Guests (checkin date, checkout date, FullName varchar(50))INSERT INTO #GuestsSELECT '10/12/2012', '10/16/2012', 'Corky Doe' U-NION ALLSELECT '12/12/2012', '12/17/2012', 'Janice Doe' U-NION ALLSELECT '11/12/2012', '11/24/2012', 'Howard Stern' U-NION ALLSELECT '12/12/2012', '12/13/2012', 'Abagail Johnson' U-NION ALLSELECT '12/12/2012', '11/15/2012', 'Teddy Sanft' U-NION ALLSELECT '12/12/2012', '11/18/2012', 'John Overton' U-NION ALLSELECT '12/12/2012', '12/19/2012', 'Sally Jenson' U-NION ALLSELECT '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 ServedWHERE ByMOnth = 12D-ROP TABLE #Guests[/code][b]Note[/b] :: The hyphens are added to some of the SQL keywords to avoid setting off my proxy server...Fri, 25 Jan 2013 16:39:41 GMTMyDoggieJessiehttp://www.sqlservercentral.com/Forums/Topic1411940-392-1.aspx[quote][b]Maple07-598122 (1/25/2013)[/b][hr]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[/quote]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.Fri, 25 Jan 2013 15:23:24 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic1411940-392-1.aspxI 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?ThanksFri, 25 Jan 2013 15:01:59 GMTMaple07-598122