Data grouped by 20 minutes?

[Vidya]

Hi,
I want to get the count of rows per each 20 minutes, is that possible?

Is there a date function or any other function that I can use in Group
by clause, which will group the data of every 20 minutes and give me
the count?

Thank you.
Vidya

[Morten Mikkelsen]

Vidya wrote:

Hi,
I want to get the count of rows per each 20 minutes, is that possible?

One solution to the is to create a function in the SQL-server that you
can join with your table , i.e.

First, do
CREATE FUNCTION MinuteGenerator
(@startDate DATETIME,
@endDate DATETIME,
@interval int)
RETURNS @minuteRange TABLE (dtMinute DATETIME, dtMinuteEnd DATETIME)
AS
BEGIN
DECLARE @workMinute DATETIME
SET @workMinute = @startDate
WHILE @workMinute <= @endDate
BEGIN
INSERT INTO @minuteRange
VALUES (@workMinute,
DATEADD(ms,-3,DATEADD(minute,@interval,@workMinute)))
SET @workMinute = DATEADD(minute, @interval, @workMinute)
END
RETURN
END

Now you can use this in your queries like:
SELECT ranges.dtminute, count(myT.x)
FROM
myTable as myT
right outer join
MinuteGenerator('2005-11-28 21:40','2005-11-28 22:40',20) as ranges
on (ts.time between ranges.dtMinute and ranges.dtMinuteEnd)
GROUP BY ranges.dtMinute

Cheers,
/Morten

[Brian Cryer]

"Vidya" <vm*****@yahoo.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

Hi,
I want to get the count of rows per each 20 minutes, is that possible?

Is there a date function or any other function that I can use in Group
by clause, which will group the data of every 20 minutes and give me
the count?

Thank you.
Vidya

Assuming the time you have in each record is a datetime, then try the
following:

select Cast(cast(Group20Min as float) / 3 / 24 as DateTime),
min(LastActive),
max(lastActive),
count(*)
from
(select cast(Cast(LastActive as float) * 24 * 3 as bigint) as Group20Min, *
from Accounts) as t
group by Group20Min

this example assumes that you have a table called Accounts with a datetime
field called LastActive. This seems to work on one of my databases.
Basically works by converting datetime to an integer and then grouping on
that. DateTime is basically a hole number of days from a fixed point in
time. So to convert to hours multiply by 24, to go from hours to your 20
minute blocks multiply by 3 (because there are 3 x 20 minute blocks per
hour). Then convert it to an integer to drop any fractional part. The cast
in the select is trying to convert back the other way - I did find some
slight rounding errors creeping in here. I've included min and max only as a
sanity check.

There are other solutions, but that's how I would do it. Hope it helps.
--
Brian Cryer
www.cryer.co.uk/brian

[Hugo Kornelis]

On 28 Nov 2005 08:45:35 -0800, Vidya wrote:

Hi,
I want to get the count of rows per each 20 minutes, is that possible?

Is there a date function or any other function that I can use in Group
by clause, which will group the data of every 20 minutes and give me
the count?

Hi Vidya,

SELECT a, b,
DATEADD(minute,
20 * (DATEDIFF(minute, '20050101', theDate) / 20),
'20050101') AS StartOf20Mins,
COUNT(*), SUM(Column)
FROM YourTable
GROUP BY a, b, DATEDIFF(minute, '20050101', theDate) / 20
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)