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

[quoted text, click to view]

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,

[quoted text, click to view]

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

[quoted text, click to view]

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