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]