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