You could adjust the actual time by 30 minutes so that values that used to fall in to the 6:30-7:30 range now fall into the 6:00-7:00 range, then group by the resulting values for the whole hour. Here is a start.
-- Create a working table.
CREATE-- DROP
TABLESomeTable
(
SomeDateDATETIMENOT NULL,
SomeValueDECIMAL(12,2)NOT NULL
)
-- Add some records with a date and a random value.
INSERT
INTOSomeTable
(SomeDate, SomeValue)
SELECTDATEADD(MINUTE, 5 * N, GETDATE() - .5),
CAST(LEFT(REVERSE(CAST(RAND(N) AS VARCHAR(20))), 3) AS DECIMAL(12,2)) / 10
FROM(SELECTTOP 200 ROW_NUMBER() OVER (ORDER BY object_id) AS N FROM sys.columns) x
-- Sum the values by the hour, adjusting the hour by 30 minutes to
-- get (ie:) 6:30-7:30 into the 6:00 block.
SELECTCONVERT(VARCHAR(13), DATEADD(MINUTE, -30, SomeDate), 121) AS BlockTime,
SUM(SomeValue) AS BlockTotal
FROMSomeTable
GROUP BY CONVERT(VARCHAR(13), DATEADD(MINUTE, -30, SomeDate), 121)
ORDER BY CONVERT(VARCHAR(13), DATEADD(MINUTE, -30, SomeDate), 121)