Data grouped by 20 minutes?

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

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,@in
 terval,@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('200
5-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

"Vidya" <vmswar2@yahoo.com> wrote in message
news:1133196335.529841.201590@g43g2000cwa.googlegroups.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

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)