Grouping data by time period

Hi everyone,

I'm now on a TSQL problem and hope somebody here can help me.
I have a log data for a website that logs IP addresses and date/time of the access, and I shall group IP values by time period, beginning from the 1st access, at each 30 minutes.

Does anyone here can help me with this issue? Any tips will be very appreciated.

TIA

MarceloHere is a generic code that you should get you started.
declare @.interval int
set @.interval=30
select dateadd(minute,floor(datediff(minute,0,OrderDate)/@.interval)*@.interval,0) [dt],
count(*) [cnt]
from Northwind..Orders
group by dateadd(minute,floor(datediff(minute,0,OrderDate)/@.interval)*@.interval,0)
order by 1|||Thanks a lot, worked like a charm. Only thing I need to figure out now is how to count 30 minutes since first access from an IP address, but your code will help me a lot.

Regards,

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...
>