Posted by Morten Mikkelsen on 11/28/05 22:46

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

[Back to original message]