select avg(svalue), [b]dateadd(hour, datediff(hour, 0, dt), 0)[b] as dte
from   values
where dt > dateadd(month,-24,getdate())
group by [b]dateadd(hour, datediff(hour, 0, dt), 0)[b]
order by dte

select avg(svalue), [b]dateadd(day, datediff(day, 0, dt), 0)[b] as dte
from   values
where dt > dateadd(month,-24,getdate())
group by [b]dateadd(day, datediff(day, 0, dt), 0)[b]
order by dte

declare @StartDt  datetime
select @StartDt = '20050101'

declare @t table(
MidPointDate datetime not null, 
StartDate datetime not null, 
EndDate datetime not null)


-- Load a table with the Midpoint, Start, and End for each time period
insert into @t
select
	MidPointDate =
		--Compute Mid point between Start Date and End Date
		dateadd(ss,datediff(ss,a.StartDate,a.EndDate)/2,a.StartDate),
	StartDate,
	EndDate
from
	(
	select
		StartDate = dateadd(mm,aa.number,@StartDt),
		EndDate = dateadd(mm,aa.number+1,@StartDt)
	from
		-- Function from SQL Team Script Library Forum
		dbo.F_TABLE_NUMBER_RANGE(0,11) aa
	) a



-- Join Value table to Date table to get average for each period
select
	b.MidPointDate,
	AcerageValue = avg(isnull(a.value,0))
from
	@t b
	left join
	MyValueTable a
	on a.DT >= b.StartDate and a.DT < b.EndDate
group by
	b.MidPointDate
order by
	b.MidPointDate

quote:

---

Originally posted by kb

Thanks for the link KH.

I had a similar idea myself, but have been laid up with food poisoning for a while and in the meantime the goalposts have moved. Looks like I can now get away with using the crossover boundary, e.g. midnight last day of month, instead of the median of the month. This is, of course, much easier to accomplish :-)

But the original problem is going to eat away at me so I'll come back to it sooner or later when I have chance and look at joining the date table in.

Thanks for taking the time to help out with this - much appreciated.

Kevin

---