SELECT [Day], MaleGuests=ISNULL(SUM(CASE Gender WHEN 'M' THEN 1 END), 0) , FemaleGuests=ISNULL(SUM(CASE Gender WHEN 'F' THEN 1 END), 0)

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