Census, datepart, and spanning midnight

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Census, datepart, and spanning midnight

Author

Topic

bdnum
Starting Member

1 Post

Posted - 2006-11-20 : 10:28:00

I've hit a wall, and I need a little help.

My table has arrival and departure data for people at a building, and these times often span midnight. I'm trying to come up with an average census for each hour of the day. (Census, or # of people present = number of people who arrive before a particular time and leave after this time). Determining a point census would be:

select count(*)
from timedata
where censustime between timearrival and timedeparted

However, determining the average census for each hour of the day (9am, 10am, 11am, noon, etc.) is a bit tricker, but not too much:

declare @hour int
set @hour = 0
while @hour < 24
begin
select @hour, avg(pointcensus)
from
(select datepart(dy,timearrival) as dayofyear, count(timedeparted) as pointcensus
from timedata
where @hour between datepart(hour,timearrival) and datepart(hour,timedeparted)
and timearrival > '1/1/6'
group by datepart(dy,timearrival)) as datapool
set @hour = @hour + 1
end

Problem is, if I'm looking at 1am, it won't pick up somebody who shows up at 11pm and leaves at 2am (but who should obviously be counted). Same thing happens if I want to look at 11pm, and arrival = 10pm and departure = 2am. Does anyone have any suggestions on how to fix this? Also, if you have a better way to write the above, I'm all ears! Thanks!

Kristen
Test

22859 Posts

Posted - 2006-11-20 : 10:39:28

I reckon for a given hour you want people where:


    DATEADD(Hour, DATEDIFF(Hour, 0, timearrival), 0) <= @StartTime @PointTime
AND DATEADD(Hour, DATEDIFF(Hour, 0, timedeparted), 0) >= @EndTime @PointTime

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources