Posted by Brian Cryer on 11/29/05 13:17

"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

[Back to original message]