June 12, 2007 at 5:21 pm
Hi -
I need to get the average total of values for each 15 minute period that are reported every minute.
The only two fields are datetime and values(int). None of my grouping options or subqueries or CASE trials produce desired results. Can you help get me on the right path? Thanks.
June 12, 2007 at 7:25 pm
For this type of problem, the solution requires a table of numbers from zero to some large value. See
create table Events
(EventTsdatetimenot null
, EventMeasureintnot null
, constraint Events_P primary key (EventTs)
)
go
declare@PeriodDatedatetime
--Set to midnight of today.
set@PeriodDate = cast( cast ( ( getdate() - .5) as integer ) as datetime )
selectPeriodStartTS
,AVG(EventMeasure)
from(select DATEADD( mi, Number * 15 , @PeriodDate )AS PeriodStartTS
,DATEADD( mi, ( Number + 1 ) * 15 , @PeriodDate )AS PeriodEndTS
from Numbers
-- 24 hours in a day each having 4 intervals
whereNumber between 0 and ( ( 4 * 24) - 1 )
) as Ranges
-- LOJ for no events during a time period
left outer join Events
on Events.EventTs between Ranges.PeriodStartTS and Ranges.PeriodEndTS
group by Ranges.PeriodStartTS
SQL = Scarcely Qualifies as a Language
June 14, 2007 at 6:15 am
It seems to me that the approach below is what you are looking for, unless I misunderstood the problem. For the data shown, it produces
2007-06-14 18:15 15
2007-06-15 18:30 30
Thus 18:15 includes everything between 18:15 and 18:29 and 18:30 includes everything between 18:30 and 18:44, etc.
create table t
(d datetime,
v int)
insert into t values('2007-06-14 18:23',10)
insert into t values('2007-06-14 18:24',20)
insert into t values('2007-06-14 18:34',30)
select gd,avg(v)
from
(
select dateadd(minute,datediff(minute,'2007-01-01',d)/15*15,'2007-01-01') gd,v
from t
) t
group by gd
order by gd
June 14, 2007 at 12:28 pm
Yet another answer -- really just a rework of the two you already got. Carl is right about the table with just a sequence of numbers. You quickly start wondering how you got along without one. I placed mine in Master so everyone can reach it and mine has two columns: N and ZN. The only difference is that N starts at 1 and ZN starts at 0. I also have a function TruncTime() which returns the supplied date with the time portion stripped away -- that is, set to midnight. Another utility I seem to use quite a lot.
Carl's solution will give you every 15-minute increment for the specified day, Michael's gives you only the increments that actually contain data but does so for all the days in the table -- though it's simple enough to change that. Whichever is best for you.
Variation on Carl's solution:
select Convert( char(17), gd, 13 ) as Interval, avg( v ) as [Interval Avg]
from (
select dateadd( minute,
datediff( minute, dbo.TruncTime( d ), d) / 15 * 15,
dbo.TruncTime( d )
) as gd, v
from t
) t
group by gd
order by gd;The Convert just makes the display nicer. The output looks like this:
Interval Interval Avg ================= =========== 12 Jun 2007 08:15 15 12 Jun 2007 08:30 0 12 Jun 2007 08:45 88 13 Jun 2007 18:15 15 13 Jun 2007 18:30 30 14 Jun 2007 18:15 15 14 Jun 2007 18:30 30
Variation on Michael's solution:
Declare @BaseDate datetime;
Set @BaseDate = '2007-06-12'; -- The day you want to see
Select Convert( char(17), Interval, 13 ), Avg( v ) as [Interval Avg]
From (
Select DateAdd( mi, ZN * 15, @BaseDate ) as Interval
From Master.dbo.Tally
Where ZN < 4 * 24) as Ints -- 4 15-minute intervals per hour for 24 hours.
Left Outer Join t
On DateDiff( second, Interval, d ) between 0 and (15 * 60) - 1
-- 15-minute intervals, 60 seconds per minute, less 1 so boundary cases aren't counted twice.
Group By Interval
Order by Interval;The output looks like this:
Interval Interval Avg ================= =========== 12 Jun 2007 00:00 NULL 12 Jun 2007 00:15 NULL 12 Jun 2007 00:30 NULL 12 Jun 2007 00:45 NULL * * * 12 Jun 2007 08:00 NULL 12 Jun 2007 08:15 15 12 Jun 2007 08:30 0 12 Jun 2007 08:45 88 12 Jun 2007 09:00 NULL * * * 12 Jun 2007 23:00 NULL 12 Jun 2007 23:15 NULL 12 Jun 2007 23:30 NULL 12 Jun 2007 23:45 NULL
The 88 value at 08:45 is a single entry for exactly 08:45. Just checking the boundary conditions.
Oh, almost forgot the table dump:
d v ======================= == 2007-06-12 08:23:00.00010 2007-06-12 08:24:00.00020 2007-06-12 08:34:00.0000 2007-06-12 08:45:00.00088 2007-06-13 18:23:00.00010 2007-06-13 18:24:00.00020 2007-06-13 18:34:00.00030 2007-06-14 18:23:00.00010 2007-06-14 18:24:00.00020 2007-06-14 18:34:00.00030
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
June 14, 2007 at 5:29 pm
Thanks very much to Carl, Michael and Tomm for supplying your suggestions, all of which will take some experimentation on my part to verify as I have a good learning curve just to start. In the meantime while testing the solution I discovered the application I was asked to query against already includes this function, so I will be testing these results out under a lot less pressure. Thanks again I really appreciate your help.
Kenena
June 15, 2007 at 11:09 am
Tomm,
How about showing us that TruncTime function. I know it's easy and thus all I will do is just open up a can of worms as to how efficient or inefficient it is. In any case, I have lapsed into just writing in-line code such as:
convert(datetime,substring(convert(varchar,mydate,120),1,10))
Oracle always had, and still has, this nice trunc function on date/numeric columns/variables.
June 15, 2007 at 11:32 am
Here it is right out of the db:
/****** Object: UserDefinedFunction [dbo].[TruncTime] Script Date: 06/15/2007 10:12:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
=============================================
Author: Tomm Carr
Create date: 2007/04/16
Description: Truncates the time portion from the parameter. In effect, sets the date to midnight.
So, for example, if the date passed in was '04/16/2007 12:33:15.993' then the
date returned will be '04/16/2007 00:00:00'.
=============================================
*/
CREATE FUNCTION [dbo].[TruncTime] (
@pDate datetime -- We will return only the date part of this value
)
RETURNS datetime
AS
BEGIN
DECLARE @Result datetime;
-- Convert the date to a string formatted for date only (no time portion).
-- Storing it into the datetime variable performs an implicit conversion
-- back into datetime -- but with zeros for all the time values. This is
-- the same result as when you define a datetime variable and store a date
-- string to it:
-- declare @DateVar datetime;
-- set @DateVar = '09/15/2006'; -- No time portion
SELECT @Result = Convert( varchar, @pDate, 101 );
-- That's all there is.
RETURN @Result;
END--functionBefore anyone goes to the trouble of pointing out the fact that the function could be reduced to one line "return Convert( varchar, @pDate, 101 );", let me just say this: I know. That's just my coding standard.
Also, why format 101? No particular reason, there are many that could have been used. If you have a favorite, use it.
I make lots of simple functions like this because using them makes code a lot easier to understand than the inline stuff.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply