[Query] Help

Hello,
We someone be so kind to help me out with a (probably simpel) query?
This is some stripped down sample data to illustrate:
(two fields, 1 NAME & 1 NAMEDATE)
bill 01/03/2005 06:25
eric 01/03/2005 07:25
pat 01/03/2005 07:25
eric 01/03/2005 09:25
eric 01/03/2005 10:25
bill 01/03/2005 09:25
pat 01/03/2005 06:25
pat 01/03/2005 08:25
eric 01/03/2005 09:25
bill 01/03/2005 09:25
I am trying to find the count of names per hour period and I am struggling a
bit. I tried with function 'datepart(hour, NAMEDATE)' but don't get it to
work.
The query should in a query result row ...
NAME 6hr 7hr 8hr 9hr 10hr
billl 1 2
eric 1 2 1
etc
So for 'bill' there are 3 records, 1 where hour=06 and 2 where hour=9.
Eric 4 records, 1 where hour=07; 2 where hour=09 and 1 where hour=10.
Sorry if my question & layout is a bit blurry ;)
Rgds,
T.
Try,
use northwind
go
create table t (
colA varchar(25),
colB datetime
)
go
insert into t values('bill', '1/03/2005 06:25')
insert into t values('eric', '01/03/2005 07:25')
insert into t values('pat', '01/03/2005 07:25')
insert into t values('eric', '01/03/2005 09:25')
insert into t values('eric', '01/03/2005 10:25')
insert into t values('bill', '01/03/2005 09:25')
insert into t values('pat', '01/03/2005 06:25')
insert into t values('pat', '01/03/2005 08:25')
insert into t values('eric', '01/03/2005 09:25')
insert into t values('bill', '01/03/2005 09:25')
go
select
colA,
count(case when datediff(hour, convert(char(8), colB, 112), colB) = 6 then
1 end) as [6hr],
count(case when datediff(hour, convert(char(8), colB, 112), colB) = 7 then
1 end) as [7hr],
count(case when datediff(hour, convert(char(8), colB, 112), colB) = 8 then
1 end) as [8hr],
count(case when datediff(hour, convert(char(8), colB, 112), colB) = 9 then
1 end) as [9hr],
count(case when datediff(hour, convert(char(8), colB, 112), colB) = 10 then
1 end) as [10hr]
from
t
group by
colA
order by
colA
drop table t
go
AMB
"TieTo" wrote:

> Hello,
> We someone be so kind to help me out with a (probably simpel) query?
> This is some stripped down sample data to illustrate:
> (two fields, 1 NAME & 1 NAMEDATE)
>
> bill 01/03/2005 06:25
> eric 01/03/2005 07:25
> pat 01/03/2005 07:25
> eric 01/03/2005 09:25
> eric 01/03/2005 10:25
> bill 01/03/2005 09:25
> pat 01/03/2005 06:25
> pat 01/03/2005 08:25
> eric 01/03/2005 09:25
> bill 01/03/2005 09:25
> I am trying to find the count of names per hour period and I am struggling a
> bit. I tried with function 'datepart(hour, NAMEDATE)' but don't get it to
> work.
> The query should in a query result row ...
> NAME 6hr 7hr 8hr 9hr 10hr
> billl 1 2
> eric 1 2 1
> etc
> So for 'bill' there are 3 records, 1 where hour=06 and 2 where hour=9.
> Eric 4 records, 1 where hour=07; 2 where hour=09 and 1 where hour=10.
> Sorry if my question & layout is a bit blurry ;)
> Rgds,
> T.
>
>
|||"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:352EE400-E233-4389-90A6-7A02101A1A91@.microsoft.com...
> Try,
> use northwind
> go
>
<cut>
Hey Alejandro,
Will try your proposed query. Thanks for the reply!!!!
Rgds,
T.

[Query] Help

Hello,
We someone be so kind to help me out with a (probably simpel) query?
This is some stripped down sample data to illustrate:
(two fields, 1 NAME & 1 NAMEDATE)
bill 01/03/2005 06:25
eric 01/03/2005 07:25
pat 01/03/2005 07:25
eric 01/03/2005 09:25
eric 01/03/2005 10:25
bill 01/03/2005 09:25
pat 01/03/2005 06:25
pat 01/03/2005 08:25
eric 01/03/2005 09:25
bill 01/03/2005 09:25
I am trying to find the count of names per hour period and I am struggling a
bit. I tried with function 'datepart(hour, NAMEDATE)' but don't get it to
work.
The query should in a query result row ...
NAME 6hr 7hr 8hr 9hr 10hr
---
billl 1 2
eric 1 2 1
etc
So for 'bill' there are 3 records, 1 where hour=06 and 2 where hour=9.
Eric 4 records, 1 where hour=07; 2 where hour=09 and 1 where hour=10.
Sorry if my question & layout is a bit blurry ;)
Rgds,
T.Try,
use northwind
go
create table t (
colA varchar(25),
colB datetime
)
go
insert into t values('bill', '1/03/2005 06:25')
insert into t values('eric', '01/03/2005 07:25')
insert into t values('pat', '01/03/2005 07:25')
insert into t values('eric', '01/03/2005 09:25')
insert into t values('eric', '01/03/2005 10:25')
insert into t values('bill', '01/03/2005 09:25')
insert into t values('pat', '01/03/2005 06:25')
insert into t values('pat', '01/03/2005 08:25')
insert into t values('eric', '01/03/2005 09:25')
insert into t values('bill', '01/03/2005 09:25')
go
select
colA,
count(case when datediff(hour, convert(char(8), colB, 112), colB) = 6 then
1 end) as [6hr],
count(case when datediff(hour, convert(char(8), colB, 112), colB) = 7 then
1 end) as [7hr],
count(case when datediff(hour, convert(char(8), colB, 112), colB) = 8 then
1 end) as [8hr],
count(case when datediff(hour, convert(char(8), colB, 112), colB) = 9 then
1 end) as [9hr],
count(case when datediff(hour, convert(char(8), colB, 112), colB) = 10 then
1 end) as [10hr]
from
t
group by
colA
order by
colA
drop table t
go
AMB
"TieTo" wrote:

> Hello,
> We someone be so kind to help me out with a (probably simpel) query?
> This is some stripped down sample data to illustrate:
> (two fields, 1 NAME & 1 NAMEDATE)
>
> bill 01/03/2005 06:25
> eric 01/03/2005 07:25
> pat 01/03/2005 07:25
> eric 01/03/2005 09:25
> eric 01/03/2005 10:25
> bill 01/03/2005 09:25
> pat 01/03/2005 06:25
> pat 01/03/2005 08:25
> eric 01/03/2005 09:25
> bill 01/03/2005 09:25
> I am trying to find the count of names per hour period and I am struggling
a
> bit. I tried with function 'datepart(hour, NAMEDATE)' but don't get it to
> work.
> The query should in a query result row ...
> NAME 6hr 7hr 8hr 9hr 10hr
> ---
> billl 1 2
> eric 1 2 1
> etc
> So for 'bill' there are 3 records, 1 where hour=06 and 2 where hour=9.
> Eric 4 records, 1 where hour=07; 2 where hour=09 and 1 where hour=10.
> Sorry if my question & layout is a bit blurry ;)
> Rgds,
> T.
>
>|||"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:352EE400-E233-4389-90A6-7A02101A1A91@.microsoft.com...
> Try,
> use northwind
> go
>
<cut>
Hey Alejandro,
Will try your proposed query. Thanks for the reply!!!!
Rgds,
T.