That looks excellent. Having a permanent calendar table makes absolute sense to me and your solution looks so much cleaner than mine.

I'll give it a try when I get home...

Hmm... i think i have solved it... presume the only way to get the hours is using a temp table?

I then cross join that temp table with all the source_ids...

right heres some inserts...

insert into watts_hour (date, source_id, watts_avg, watts_min, watts_max, total_readings)

SELECT 'Jan 2 2010 9:00AM','394D9190-0196-4926-B952-6F72966FAD6A','1620.0000000000','1553','1701','48' UNION ALL

SELECT 'Jan 2 2010 10:00AM','394D9190-0196-4926-B952-6F72966FAD6A','1953.2500000000','1558','4532','518' UNION ALL

SELECT 'Jan 2...

Thanks for the responses guys.. much appreciated. I think i didnt quite explain it enough.

the data in the tables is energy usage per hour. table A is generated from second...