I have a function that groups date time information by an interval of
time(in minutes).
The function is : udf_interval_format(@dt datetime,@interval int)
so if you wanted to count the number of rows in an interval you would use:
select utility.dbo.udf_interval_format(yourdatetimefield,2) as
yourint,count(*) as ct from yourtable
group by utility.dbo.udf_interval_format(yourdatetimefield,2)
order by utility.dbo.udf_interval_format(yourdatetimefield,2)
which would group data every 2 mins.
the output of the function is hh:mm
Here is the function
CREATE FUNCTION udf_interval_format(@dt datetime,@interval int)
RETURNS varchar(5) AS
BEGIN
DECLARE @pieces int
DECLARE @st varchar(5)
DECLARE @hourpart int
DECLARE @minpart int
set @pieces = (60*datepart(hour,@dt) +datepart(mi,@dt) )/@interval
set @hourpart= @pieces*@interval/60
set @minpart =@pieces*@interval - @hourpart*60
set @st= right('00'+cast(@hourpart as
varchar),2)+':'+right('00'+cast(@minpart as varchar),2)
return @st
END
----- Original Message -----
From: "Neil H." <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Friday, January 11, 2002 3:54 PM
Subject: Times...
> Here is a SQL question for yall...
>
> I have about 3000 rows a day which have a datetime column. I want to be
> able to graph the number of rows every 2 minutes. I know it could be done
> with a lot of looping in CF but what is the best way to tackle this one?
>
> Thanks,
>
> Neil
>
______________________________________________________________________
Why Share?
Dedicated Win 2000 Server � PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation � $99/Month � Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
