SQL Server 2000 grouping scenario, every 3 hours

Question

I have a scenario I need help with. I have to create a sql query using SQL Server 2000 that will do the following:

I have data that looks like the table below. I need to find a way to determine an average temparture every 3 hours. The tricky part is the every 3 hour grouping should be like the following:

1st Average Grouping - (03/01/2013 13:00, 03/01/2013 14:00, 03/01/15:00)
2nd Average Grouping - (03/01/2013 14:00, 03/01/2013 15:00, 03/01/16:00)
3rd Average Grouping - (03/01/2013 15:00, 03/01/2013 16:00, 03/01/17:00)


Date        Time        Temperature
03/01/2013  13:00            75
03/01/2013  14:00            80
03/01/2013  15:00            82
03/01/2013  16:00            82
03/01/2013  17:00            81
03/01/2013  18:00            80

Its a weird use case and a bit diffult to put down on paper. Its an average of 3 hours, but every hour??

I would greatly appreciate any ideas on this.

Answer 1

In SQL Server 2000, you would do this with a join. However, to make the joins easier and to dispense with the date arithmentic, I'm going to first define a join key.

select t1.date, t2.time, avg(t2.temperature) as avgTemp
from (select t.*,
             (select count(*) from t t2 where t2.date < t.date or t2.date = t.date and t2.time <= t.time
             ) as seqnum
      from t
     ) t1 left outer join
     (select t.*,
             (select count(*) from t t2 where t2.date < t.date or t2.date = t.date and t2.time <= t.time
             ) as seqnum
      from t
     ) t2
     on t.seqnum between t2.seqnum - 2 and t.seqnum
group by t1.date, t1.time

This is much easier in more recent versions of SQL Server (because they support window functions). You might consider upgrading.