• 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)