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!

Show quoteHide quote

Question about functions
Implicit conversion from string to date
Exporting Stored Procs
Speeding up queries using TOP
LIKE for a Numeric?