-
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
-
try something like
select count(badge), datepart(hh,timedate)
from mytable
group by badge, timedate
order by timedate
-
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!
-
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.
-
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
-
Forum Rules
|
|