Microsoft SQL Server forum - Data grouped by 20 minutes?

Author

Data grouped by 20 minutes?

Vidya

2005-11-28, 11:24 am

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

2005-11-28, 8:25 pm

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

Brian Cryer

2005-11-29, 7:23 am

"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

Hugo Kornelis

2005-11-29, 8:24 pm

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)

Sponsored Links