Convert Datetime Interval

I am currently using the code below to convert data collected in 1 minute intervals to other time intervals (e.g., 5 minute intervals). The code works fine except for one thing. I would like the DateTime value in the new interval to coincide with the end DateTime value of that interval rather than the ceiling DateTime value (the values of Field1 thru Field4 must be extracted from existing data). The problem occurs when there is no data for a particular 1 minute time period. An example might make this more clear (in this case 1 min data is converted to 5 min data).

'2007-09-30T08:01:00'
'2007-09-30T08:02:00'
'2007-09-30T08:03:00'
'2007-09-30T08:04:00'
'2007-09-30T08:05:00'
Above values are used to create a 5 min interval record with
DateTimeValue for this record (in DestTableName) = '2007-09-30T08:05:00'
That's how it should work. However, missing data causes a problem
in the next 5 min time interval.

'2007-09-30T08:06:00'
'2007-09-30T08:07:00'
'2007-09-30T08:08:00'
'2007-09-30T08:09:00'
Missing value at '2007-09-30T08:10:00'
Above values are used to create a 5 min interval record with
DateTimeValue for this record (in DestTableName) = '2007-09-30T08:09:00'

Instead, I want the DateTime value in DestTableName to be '2007-09-30T08:10:00'

The values in Field1 - Field4 in DestTable have to be drawn from existing data; the only 'artificial' data would be the DateTime time value needed to coincide with the time interval.



USE tempdb;


IF OBJECT_ID('#Data') IS NOT NULL

DROP TABLE #Data;

IF OBJECT_ID('#Grouped') IS NOT NULL

DROP TABLE #Grouped;


DECLARE @dtStart datetime, @dtEnd datetime, @dtDivisor integer;


SET @dtStart = '2007-07-01T08:01:00';

SET @dtEnd = '2007-09-30T16:30:00';

SET @dtDivisor = 5


Select

[DateTime],

[Field1],

[Field2],

[Field3],

[Field4],

CEILING(DATEDIFF(minute,@dtStart,[DateTime])/@dtDivisor) Frame

Into #Data

From

[SourceDbName].[SourceSchemaName].[SourceTableName]

WHERE

(convert(varchar, [DateTime],126) BETWEEN '2007/07/01' AND '2007/09/30') AND

(convert(varchar, [DateTime],114) BETWEEN '08:01:00' AND '16:30:00')

Select

Frame,

MAX(t1.[DateTime]) [Max_DateTime],

MIN(t1.[DateTime]) [Min_DateTime],

MAX(t1.[Field2]) AS [Field2],

MIN(t1.[Field3]) AS [Field3]

Into #Grouped

From

#Data t1

Group By

Frame

Select

Opn.[DateTime],

[Field1],

[Field2],

[Field3],

[Field4]

Into

[DestDbName].[DestSchemaName].[DestTableName]

From

( Select

G.[Frame],

G.[Max_DateTime] as [DateTime],

D.[Field1],

G.[Field2],

G.[Field3]

From

#Data D

Join #Grouped G On D.[DateTime] = G.[Min_DateTime] And D.Frame = G.Frame

) as Opn Join

( Select

G.[Frame],

G.[Max_DateTime] as [DateTime],

D.[Field4]

From

#Data D

Join #Grouped G On D.[DateTime] = G.[Max_DateTime] And D.Frame = G.Frame

) as Cls On Opn.Frame = Opn.Frame And Opn.[DateTime] = Cls.[DateTime]