Help on how to count guests in hotel every day

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

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

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.