Counting People in time

I'm trying to count the number of people that I have in a building at any
given hour from a table that basically looks like this...
CREATE TABLE InOutTimes
(
In_Date smalldatetime NOT NULL,
Out_Date datetime NOT NULL
)
go
INSERT InOutTimes VALUES ('8/1/01 08:00:00', '8/1/01 10:00:00')
INSERT InOutTimes VALUES ('8/1/01 08:00:00', '8/1/01 09:00:00')
INSERT InOutTimes VALUES ('8/1/01 13:00:00', '8/1/01 13:30:00')
INSERT InOutTimes VALUES ('8/1/01 17:00:00', '8/1/01 20:00:00')
INSERT InOutTimes VALUES ('8/2/01 09:00:00', '8/2/01 12:00:00')
INSERT InOutTimes VALUES ('8/2/01 10:00:00', '8/2/01 11:00:00')
INSERT InOutTimes VALUES ('8/2/01 16:00:00', '8/2/01 23:00:00')
INSERT InOutTimes VALUES ('8/2/01 17:00:00', '8/3/01 01:00:00')
I need something that will output something like this...
00 1
01 1
02 0
03 0
04 0
05 0
06 0
07 0
08 2
09 3
10 3
11 2
12 1
13 1
14 0
15 0
16 1
17 3
18 3
19 3
20 3
21 2
22 2
23 2
I have the 0 - 23 hour in memory that I can update, but my issue is when the
in/out times span a day. Is there a way I can do all this without a cursor
and case statements?
ANY help is appreciated.
Thanks
Scott
BTW - Wish me luck with the hurricane!!Hello, Scott
Try this:
CREATE TABLE #Numbers (N int PRIMARY KEY)
INSERT INTO #Numbers
SELECT DISTINCT number FROM master..spt_values
WHERE number BETWEEN 0 AND 23
SELECT N as TheHour, (
SELECT COUNT(*) FROM InOutTimes
WHERE DATEDIFF(d,In_Date,Out_Date)=0
AND N BETWEEN DATEPART(hour,In_Date) AND DATEPART(hour,Out_Date)
OR DATEDIFF(d,In_Date,Out_Date)=1
AND (N>=DATEPART(hour,In_Date) OR N<=DATEPART(hour,Out_Date))
OR DATEDIFF(d,In_Date,Out_Date)>1
) AS Cnt
FROM #Numbers ORDER BY N
Razvan
PS. Good luck with the hurricane! You are in Lafayette LA, right?|||That look like just what I needed! Awesome! Many thanks. I'll post in a
few days and let you know if it works out in the report :)
Yeah, Lafayette. They are expecting it to hit about 70 miles West of us so
I'm hauling to Atlanta in a bit. Have good one!
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1127407645.880484.6580@.g14g2000cwa.googlegroups.com...
> Hello, Scott
> Try this:
> CREATE TABLE #Numbers (N int PRIMARY KEY)
> INSERT INTO #Numbers
> SELECT DISTINCT number FROM master..spt_values
> WHERE number BETWEEN 0 AND 23
> SELECT N as TheHour, (
> SELECT COUNT(*) FROM InOutTimes
> WHERE DATEDIFF(d,In_Date,Out_Date)=0
> AND N BETWEEN DATEPART(hour,In_Date) AND DATEPART(hour,Out_Date)
> OR DATEDIFF(d,In_Date,Out_Date)=1
> AND (N>=DATEPART(hour,In_Date) OR N<=DATEPART(hour,Out_Date))
> OR DATEDIFF(d,In_Date,Out_Date)>1
> ) AS Cnt
> FROM #Numbers ORDER BY N
> Razvan
> PS. Good luck with the hurricane! You are in Lafayette LA, right?
>