Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2002
    Posts
    3

    Unanswered: 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 Fiorini

  2. #2
    Join Date
    Apr 2002
    Location
    England
    Posts
    21
    Is 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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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/

  4. #4
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78

    Re: Interesting Grouping problem...

    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 Abel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •