Summing A Column, Using A Pivot Table, Accounting For NULL

hello, i'm using sql200 and i am attempting to create a table that has an hourly-incrementing 'Date_Time' column, with a corresponding 'Total' column (which keeps a running total of values off of another table) . The code I am using right now is...

declare @date as smalldatetime

set @date = dateadd(yy, -1, cast(convert(char(11), current_timestamp, 101) + '00:00:00' as smalldatetime))



select dateadd(hh, i, @date) as Date_Time, sum(Subtotal) as Total

into #POGtable

from Pivot, OrderGroup

where

i between 0 and 24 and

CreationDate between @date and dateadd(hh, i, @date)

group by i





select dateadd(hh, i, @date) as Date_Time, 0 as Total

into #Ptable

from Pivot

where i between 0 and 24

group by i



select *

from #POGtable

union

select * from #Ptable p

where not exists(

select * from #POGtable pog

where p.Date_Time >= pog.Date_Time)



the solution is ugly, but the problem i'm having is that values for 'SubTotal' don't usually appear before 8 or 9 am. what you see above is me getting all the times (hours) that a subtotal present, creating another table with every possible hour in it (and with a 'Total' column as just zero), and then combining the two tables to create one flowing table over a 24-hour period.



there has GOT to be a better way to do this; the main point being that i want the sum( ) function to start adding up values immediately so i don't have to union two tables