ID --- Monday --- Tuesday and so on
1  --- A,B,C ---  A,B,C
2  --- A,B,C ---  A,B,C
3  --- A,B,C ---  A,B,C

DECLARE @tblReadings TABLE
(
	inReadingID int IDENTITY,
	inMonitoredItemID int,
	dtTimestamp datetime,
	flReading float
	PRIMARY KEY (inReadingID)
)

DECLARE @inNumDaysData int
DECLARE @inNumReadingsPerDay int
DECLARE @inNumItemsBeingMonitored int

SET @inNumDaysData = 10
SET @inNumReadingsPerDay = 10
SET @inNumItemsBeingMonitored = 2

DECLARE @inDayCounter int
DECLARE @dtStartDate datetime
DECLARE @inMonitoredItemsCounter int
DECLARE @inNumMinsBetweenReadings int
DECLARE @inReadingsCounter int
DECLARE @dtReadingTimestamp datetime
DECLARE @flRandomNumber float
DECLARE @flReading float

--calculate the number of minutes between readings
SET @inNumMinsBetweenReadings = 1440 / @inNumReadingsPerDay

--loop through the days
SET @inDayCounter = @inNumDaysData
WHILE @inDayCounter > 1
BEGIN
	--calculate the date
	SET @dtStartDate = DATEADD(dd, -@inDayCounter, CAST(CAST(YEAR(GETDATE()) AS varchar) + '-' + CAST(MONTH(GETDATE()) AS varchar) + '-' + CAST(DAY(GETDATE()) AS varchar) AS datetime))		

	--loop through the number of items being monitored
	SET @inMonitoredItemsCounter = 1
	WHILE @inMonitoredItemsCounter <= @inNumItemsBeingMonitored
	BEGIN
		--loop through the minutes
		SET @inReadingsCounter = 1
		WHILE @inReadingsCounter <= @inNumReadingsPerDay
		BEGIN
			--generate the reading timestamp
			SET @dtReadingTimestamp = DATEADD(mi, (@inNumMinsBetweenReadings * @inReadingsCounter), @dtStartDate)
	
			--generate a random reading
			SET @flRandomNumber = RAND()
			SELECT @flReading = ((30 + 1) - 5) * @flRandomNumber + 5
		
			--insert the data into the readings table
			INSERT INTO @tblReadings (inMonitoredItemID, dtTimestamp, flReading)
			VALUES (@inMonitoredItemsCounter, @dtReadingTimestamp, @flReading)
	
			--increment the readings counter
			SET @inReadingsCounter = @inReadingsCounter + 1
		END

		--increment the monitored items counter
		SET @inMonitoredItemsCounter = @inMonitoredItemsCounter + 1
	END

	--decrement the day counter to move closer to today
	SET @inDayCounter = @inDayCounter - 1
END

--select the average reading per day
SELECT inMonitoredItemID, CAST(CAST(YEAR(dtTimestamp) AS varchar) + '-' + CAST(MONTH(dtTimestamp) AS varchar) + '-' + CAST(DAY(dtTimestamp) AS varchar) AS datetime) As dtTimestamp, AVG(flReading) As flReading
FROM @tblReadings
WHERE dtTimestamp > DATEADD(dd, -7, GETDATE())
GROUP BY inMonitoredItemID, CAST(CAST(YEAR(dtTimestamp) AS varchar) + '-' + CAST(MONTH(dtTimestamp) AS varchar) + '-' + CAST(DAY(dtTimestamp) AS varchar) AS datetime)
ORDER BY inMonitoredItemID, CAST(CAST(YEAR(dtTimestamp) AS varchar) + '-' + CAST(MONTH(dtTimestamp) AS varchar) + '-' + CAST(DAY(dtTimestamp) AS varchar) AS datetime) DESC

quote:

---

- 07:30 for the last 4 hours
- 15:00 for the last 4 hours
- 20:30 for the last 4 hours

---

--daily average
select inMonitoredItemID
	,dateadd(day, datediff(day,0,dtTimestamp),0)
	,AVG(flReading) As flReading
from	@tblReadings
where	datediff(day,dtTimestamp,getdate()) <= 7
group by inMonitoredItemID
	,dateadd(day, datediff(day,0,dtTimestamp),0)
order by 1,2 desc

--daily (4 hour segments) average
select inMonitoredItemID
	,dateadd(hour, 4*(datediff(hour,0,dtTimestamp)%4),dateadd(day, datediff(day,0,dtTimestamp),0))
	,AVG(flReading) As flReading
from	@tblReadings
where	datediff(day,dtTimestamp,getdate()) <= 7
group by inMonitoredItemID
	,dateadd(hour, 4*(datediff(hour,0,dtTimestamp)%4),dateadd(day, datediff(day,0,dtTimestamp),0))
order by 1,2 desc

dateadd(day, datediff(day,0,dtTimestamp),0)

dateadd(hour, 4*(datediff(hour,0,dtTimestamp)%4),dateadd(day, datediff(day,0,dtTimestamp),0))

select 	getdate() [getdate()]
	,convert(datetime, 0) [convert(datetime, 0)]
	,datediff(day,0,getdate()) [datediff(day,0,getdate())]
	,dateadd(day, datediff(day,0,getdate()),0) [dateadd(day, datediff(day,0,getdate()),0)]


getdate()                   convert(datetime, 0)       datediff(day,0,getdate()) dateadd(day, datediff(day,0,getdate()),0)
-------------------------------------------------------------------------------- -----------------------------------------
2006-05-18 09:27:49.020     1900-01-01 00:00:00.000    38853                     2006-05-18 00:00:00.000

--and here is another one this solution requires:
if objectproperty(object_id('dbo.f_date_from_datetime'),'IsScalarFunction') = 1
	begin drop function dbo.f_date_from_datetime end
go
create function dbo.f_date_from_datetime (@day datetime)
returns datetime
as
begin
	return dateadd(day, datediff(day,0,@day), 0)
end
go

declare @time1dur int --minutes from 12:00 am
	,@time1end datetime
	,@time2dur int --minutes from 12:00 am
	,@time2end datetime
	,@time3dur int --minutes from 12:00 am
	,@time3end datetime
select	@time1dur = 240
	,@time1end = '1900-01-01 07:30:0.000'
	,@time2dur = 240
	,@time2end = '1900-01-01 15:00:0.000'
	,@time3dur = 240
	,@time3end = '1900-01-01 20:30:0.000'

select inMonitoredItemID
	,dtTimestamp
	,AVG(flReading) As flreading
from	(
	select	inMonitoredItemID
		,case 
		when dbo.F_time_from_datetime(dtTimestamp) between dateadd(minute,-1*@time1dur, @time1end) and @time1end then dbo.f_date_from_datetime(dtTimestamp) + @time1end
		when dbo.F_time_from_datetime(dtTimestamp) between dateadd(minute,-1*@time2dur, @time2end) and @time2end then dbo.f_date_from_datetime(dtTimestamp) + @time2end
		when dbo.F_time_from_datetime(dtTimestamp) between dateadd(minute,-1*@time3dur, @time3end) and @time3end then dbo.f_date_from_datetime(dtTimestamp) + @time3end
		end as dtTimestamp
		,flReading
	from	@tblReadings
	where	datediff(day,dtTimestamp,getdate()) <= 7
	) a
where	dtTimestamp is not null
group by inMonitoredItemID
	,dtTimestamp