Grouping by Time

Hello,
I am trying to create a query where I can group by the time of day something
happens.
For example, somebody (we don't care who) does something ( 'ev' below). We
capture the date and time this thing happens.
For analysis, a doctor wants to know what times the day these things are
happening. The grouping would be by the hour, counting the number of times
a specific thing happens.
I am not sure how to represent the hourly range. Maybe by a number ? For
example, 12:00 am to 1 am would be '1'. Not sure. I need some advice here.
CREATE TABLE [dbo].[YourTable] (
[dt] [datetime] NOT NULL ,
[ev] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO YourTable VALUES ('2004-02-10T09:00:00.000', 2)
INSERT INTO YourTable VALUES ('2004-02-10T10:00:00.000', 1)
INSERT INTO YourTable VALUES ('2004-02-10T09:30:00.000', 2)
INSERT INTO YourTable VALUES ('2004-02-10T11:00:00.000', 1)
INSERT INTO YourTable VALUES ('2004-02-10T11:05:00.000', 1)
Basically, the output should be
time range ev number of ev's in the time range.
I tried this query, but did not work.
SELECT ev, MIN(dt), COUNT(*)
FROM YourTable
GROUP BY ev, DATEDIFF(HH,'20000101',dt)
Thanks for your time.What about that ?
Select ev,DATEPART(hh,dt),count(*)
From YourTable
Group by ev,DATEPART(hh,dt)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Jack" <jack@.jack.net> schrieb im Newsbeitrag
news:Zasge.6007$Ay3.501@.lakeread06...
> Hello,
> I am trying to create a query where I can group by the time of day
> something happens.
> For example, somebody (we don't care who) does something ( 'ev' below).
> We capture the date and time this thing happens.
> For analysis, a doctor wants to know what times the day these things are
> happening. The grouping would be by the hour, counting the number of
> times a specific thing happens.
> I am not sure how to represent the hourly range. Maybe by a number ? For
> example, 12:00 am to 1 am would be '1'. Not sure. I need some advice
> here.
> CREATE TABLE [dbo].[YourTable] (
> [dt] [datetime] NOT NULL ,
> [ev] [int] NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO YourTable VALUES ('2004-02-10T09:00:00.000', 2)
> INSERT INTO YourTable VALUES ('2004-02-10T10:00:00.000', 1)
> INSERT INTO YourTable VALUES ('2004-02-10T09:30:00.000', 2)
> INSERT INTO YourTable VALUES ('2004-02-10T11:00:00.000', 1)
> INSERT INTO YourTable VALUES ('2004-02-10T11:05:00.000', 1)
> Basically, the output should be
> time range ev number of ev's in the time range.
> I tried this query, but did not work.
> SELECT ev, MIN(dt), COUNT(*)
> FROM YourTable
> GROUP BY ev, DATEDIFF(HH,'20000101',dt)
> Thanks for your time.
>|||Try,
use northwind
go
CREATE TABLE [dbo].[YourTable] (
[dt] [datetime] NOT NULL ,
[ev] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO YourTable VALUES ('2004-02-10T09:00:00.000', 2)
INSERT INTO YourTable VALUES ('2004-02-10T10:00:00.000', 1)
INSERT INTO YourTable VALUES ('2004-02-10T09:30:00.000', 2)
INSERT INTO YourTable VALUES ('2004-02-10T11:00:00.000', 1)
INSERT INTO YourTable VALUES ('2004-02-10T11:05:00.000', 1)
SELECT
ev,
MIN(dt),
COUNT(*)
FROM
YourTable
GROUP BY
ev,
convert(char(13), dt, 126)
drop table YourTable
AMB
"Jack" wrote:

> Hello,
> I am trying to create a query where I can group by the time of day somethi
ng
> happens.
> For example, somebody (we don't care who) does something ( 'ev' below). W
e
> capture the date and time this thing happens.
> For analysis, a doctor wants to know what times the day these things are
> happening. The grouping would be by the hour, counting the number of time
s
> a specific thing happens.
> I am not sure how to represent the hourly range. Maybe by a number ? For
> example, 12:00 am to 1 am would be '1'. Not sure. I need some advice here
.
> CREATE TABLE [dbo].[YourTable] (
> [dt] [datetime] NOT NULL ,
> [ev] [int] NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO YourTable VALUES ('2004-02-10T09:00:00.000', 2)
> INSERT INTO YourTable VALUES ('2004-02-10T10:00:00.000', 1)
> INSERT INTO YourTable VALUES ('2004-02-10T09:30:00.000', 2)
> INSERT INTO YourTable VALUES ('2004-02-10T11:00:00.000', 1)
> INSERT INTO YourTable VALUES ('2004-02-10T11:05:00.000', 1)
> Basically, the output should be
> time range ev number of ev's in the time range.
> I tried this query, but did not work.
> SELECT ev, MIN(dt), COUNT(*)
> FROM YourTable
> GROUP BY ev, DATEDIFF(HH,'20000101',dt)
> Thanks for your time.
>
>|||That works great. Thank you.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uOyubrlVFHA.628@.TK2MSFTNGP09.phx.gbl...
> What about that ?
> Select ev,DATEPART(hh,dt),count(*)
> From YourTable
> Group by ev,DATEPART(hh,dt)
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Jack" <jack@.jack.net> schrieb im Newsbeitrag
> news:Zasge.6007$Ay3.501@.lakeread06...
>