so I've got this table with data in it, spanning to Nov 05. The data includes the hour of the day, day of the week, a location and transactions.
What I need to do it add a column that sums the transactions over the last 180 days based on that location, hour of day and day of week.
I thought this would do it, but I was wrong..
select sum( trans_tot ) over ( partition by to_char( u.transhour, 'D' ), to_char( u.transhour, 'hh24' ), location ) tot_trans, u.dow, u.hod, location, u.transhour
from utrans_hour_stage u
where trunc(transhour) between trunc(transhour) - 180 and trunc(transhour)
to get the total transactions I can do
select sum(trans_tot) as tt
from utrans_hour_stage
where dow = 2 and hod = 10 and location = 1132
and trunc(transhour) between to_date('05/7/2007','mm/dd/yyyy') - 180 and to_date('05/7/2007','mm/dd/yyyy')
then get the number of Tuesdays (dow = 2) and the number of 10AMs (hod = 10) over the past 180 day and divide that into the "tt"
But my question is how do I update a column named "avg180" that will automatically have those number in it?
Thanks