Results 1 to 5 of 5

Thread: counting items per hour

  1. #1
    Join Date
    Aug 2005
    Posts
    31

    counting items per hour

    I am trying to get a count of how many times a badge is entered in each hour it appears. In other words, I would like to find out how many times badge 3333 comes up in each hour of the day, the same with badge 4532.

    The task at hand is to find out productivity for items processed per hour per badge number. I know I will need to take the hour out of timedate using datepart, but the results do not look right. Would anyone have any ideas on this? Thanks!




    the table I have looks like this:

    ID Badge OrdNumber TimeDate
    611213 3333 78126794 1/10/05 9:19
    611208 4532 78127559 1/10/05 9:21
    611197 3333 78126561 1/10/05 9:29
    611190 3333 78126167 1/10/05 9:30
    611177 4532 78125302 1/10/05 9:38
    611169 3333 78125781 1/10/05 9:40

  2. #2
    Join Date
    Nov 2002
    Location
    cornwall
    Posts
    187
    try something like

    select count(badge), datepart(hh,timedate)
    from mytable
    group by badge, timedate
    order by timedate

  3. #3
    Join Date
    Aug 2005
    Posts
    31
    That does work, but is there a means of getting a final count from sql, without having to paste the results into excel and pivot it? thanks for you help!

  4. #4
    Join Date
    Nov 2002
    Location
    cornwall
    Posts
    187
    hmm, thats a bit more complex ...
    does this help ...

    use northwind

    select * from (

    select datepart(q, orderdate) as orderdate
    , sum(case(shipcountry) when 'switzerland' then 1 else 0 end) as 'Switzerland'
    , sum(case(shipcountry) when 'Denmark' then 1 else 0 end) as 'Denmark'
    , sum(case(shipcountry) when 'Norway' then 1 else 0 end) as 'Norway'
    , sum(case(shipcountry) when 'USA' then 1 else 0 end) as 'USA'
    , sum(case(shipcountry) when 'Spain' then 1 else 0 end) as 'Spain'
    , sum(case(shipcountry) when 'Finland' then 1 else 0 end) as 'Finland'
    , sum(case(shipcountry) when 'Poland' then 1 else 0 end) as 'Poland'
    , sum(case(shipcountry) when 'Germany' then 1 else 0 end) as 'Germany'
    , sum(case(shipcountry) when 'Italy' then 1 else 0 end) as 'Italy'
    , sum(case(shipcountry) when 'Sweden' then 1 else 0 end) as 'Sweden'
    , sum(case(shipcountry) when 'Brazil' then 1 else 0 end) as 'Brazil'
    , sum(case(shipcountry) when 'Austria' then 1 else 0 end) as 'Austria'
    , sum(case(shipcountry) when 'UK' then 1 else 0 end) as 'UK'
    , sum(case(shipcountry) when 'Argentina' then 1 else 0 end) as 'Argentina'
    , sum(case(shipcountry) when 'Belgium' then 1 else 0 end) as 'Belgium'
    , sum(case(shipcountry) when 'Venezuela' then 1 else 0 end) as 'Venezuela'
    , sum(case(shipcountry) when 'Canada' then 1 else 0 end) as 'Canada'
    , sum(case(shipcountry) when 'Portugal' then 1 else 0 end) as 'Portugal'
    , sum(case(shipcountry) when 'France' then 1 else 0 end) as 'France'
    , sum(case(shipcountry) when 'Mexico' then 1 else 0 end) as 'Mexico'
    , sum(case(shipcountry) when 'Ireland' then 1 else 0 end) as 'Ireland'
    from orders
    group by datepart(q,orderdate), shipcountry

    ) as mytable

    compute sum(Switzerland), sum(Denmark), sum(Norway),
    sum(USA), sum(Spain), sum(Finland), sum(Poland),
    sum(Germany), sum(Italy), sum(Sweden), sum(Brazil),
    sum(Austria),sum(UK), sum(Argentina), sum(Belgium),
    sum(Switzerland), sum(Venezuela), sum(Canada),
    sum(Portugal), sum(France), sum(Mexico), sum(Ireland)
    Last edited by fatherjack; 11-04-2005 at 05:23 AM.

  5. #5
    Join Date
    Aug 2005
    Posts
    31
    With a little massaging that seems to work, thank you!

Posting Permissions

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