Interesting Grouping problem...

Alright, I hope somebody can help me with this...

I have a database that keeps a log of people going in and out of our building. It records whether they entered or exited along with the current date/time. Now I'm trying to pull those values from the table; I want a count of the entrances, a count of the exits, and the time range, grouped by a half hour. A sample row of what I want would look like:

TimeRange Entered Exited
9:00-9:30 5 3

Does anyone know what sort of a SQL Statement I could use to do this? It's been driving me crazy! I'd like to do it all in one stored proc.

Thank ya,
Joe FioriniIs this the kind of thing you're after?

CREATE PROCEDURE [dbo].[getRecordsBetween]
(
@.startDate datetime,
@.endDate datetime = null
)
AS

if @.endDate is null
begin
set @.endDate = dateadd(minute, 30, @.startDate)
end

select *
from register
where dateIn > @.startDate and dateIn < @.endDate

RETURN

Register is the name of the table where the records are stored, and on this querey we're checking for the time signed in (rather than signed out).

Hope it helps

Dan|||you can probably do it with plain sql, no need for a stored proc

what are the columns called and what are their datatypes?

rudy
http://rudy.ca/|||It would be easier to do on an hourly basis.

You'd do something like:

select datepart(hh,TheTimestamp) as hour, Name, Type
into #t1
from SourceData

select hour,
sum (case when Type='Entry' then 1 else 0 end)as Entries,
sum (case when Type='Exit' then 1 else 0 end)as Exits,
from #t1

You could then join on a lookup table for the hour to give texts like "09:00 to 10:00"

Ask me if you need more details on this

Or

If you had Names in your input data you could group by name as follows:

select Name
sum(case when hour=1 and Type='Entry' then 1 else 0 end)as EntryHour1,
sum(case when hour=2 and Type='Entry' then 1 else 0 end)as EntryHour2,
sum(case when hour=3 and Type='Entry' then 1 else 0 end)as EntryHour3,
sum(case when hour=4 and Type='Entry' then 1 else 0 end)as EntryHour4,
...etc...
sum(case when hour=1 and Type='Exit' then 1 else 0 end)as ExitHour1,
sum(case when hour=2 and Type='Exit' then 1 else 0 end)as ExitHour2,
sum(case when hour=3 and Type='Exit' then 1 else 0 end)as ExitHour3,
sum(case when hour=4 and Type='Exit' then 1 else 0 end)as ExitHour4,
...etc...
from #t1
group by Name

Hope this helps.

- Andy Abelsql