Guest tracking (need help)

[TechGuy27]

Hey there,

I am trying to create a report which can tell me how many guests are in the building at any given time. The data I have shows check-in/out times per day. The data is gathered by a visitor lobby program which checks in/out guests and stores this information in an SQL server. I have the info linked up in crystal reports.

Here is an example of what I am trying to accomplish, using the type of data I am working with:
If three guests checked in & out at the following times:

Guest 1 4/1/2010 CheckinTime: 9:00am CheckOutTime: 3:00pm
Guest 2 4/1/2010 CheckinTime: 12:00pm CheckOutTime: 4:00pm
Guest 3 4/1/2010 CheckInTime: 2:00pm CheckOutTime: 6:00pm

Then the report would tell me:

11:00am-12:00pm = 1 guests in the building
12:00pm-1:00pm = 2 guests in the building
1:00pm-2:00pm = 2 guests in the building
2:00pm-3:00pm = 3 guests in the building
3:00pm-4:00pm = 2 guests in the building
4:00pm-5:00pm = 1 guests in the building
ext, ext..

Any ideas as to how I might accomplish this? Any help is appreciated!

 

[briangriffin]

I had a similar request for a hospital that needed an inpatient count every hour. I created a stored procedure that simply looked at admission and discharge times, then determined how many inpatients there were and stored the count and a timestamp in a table created for that purpose. I then scheduled the stored procedure to run every hour.

If you can get by with this approach then the report writes itself. If that's not an option, or if a simple snapshot isn't detailed enough for you, then try this:

Create a command object that populates a variable table with hourly values for the date range you need (I pulled the min/max from the table but you would probably use parameters to specify the beginning and end dates you want).

declare 
@v_startdate smalldatetime,
@v_enddate smalldatetime

set @v_startdate = (select min(cast(Convert(varchar, InTime, 101) as smalldatetime)) from Guests)
set @v_enddate = dateadd(hh,23,(select max(cast(Convert(varchar, OutTime, 101) as smalldatetime)) from Guests))

declare @v_table table
(Indate smalldatetime,
Outdate smalldatetime)
insert into @v_table select @v_startdate, dateadd(hh,1,@v_startdate)

declare @v_counter int
set @v_counter = 1

while @v_counter <= datediff(hh,@v_startdate,@v_enddate)
BEGIN
insert into @v_table
select dateadd(hh,@v_counter,@v_startdate), dateadd(hh,@v_counter + 1, @v_startdate)
set @v_counter = @v_counter + 1
END

select * from @v_table

Then in Crystal link the InDate from the command to your database table using a >= join, and link the OutDate from the command to the database table using a <= join.

In the report create a group based on the InDate from the command object, and specify "Hourly" grouping.

Drop the fields from the database table into the details, and create a Count summary field in the group footer. Hide the detail if you want a summary report.

 

[lbass]

You could also insert separate running totals per hour, where you do a distinctcount of guest, evaluate using a formula, e.g.,:

//{@11am-noon}:
{table.checkintime} < time(12,0,0) and //checkin < end
{tabel.checkouttime} >= time(11,0,0) //checkout > start

Reset never.

-LB