Convert monthly value to Daily average values based on calendar days

Other Tech Topics
1

Hello experts
The data reside in the database from 1-01-1980 on monthly basis and after 1-01-2000 the data on daily basis. I need to convert monthly data from 1-01-1980 to 12-31-1999 to daily average based on calendar days. The image shows in Monthly column value 127.3837073 need to convert in Daily_Gas column will be 127.3837073/31=4.109

Data .PNG326×762 3.53 KB 

select fs.name, trunc( fsc.date_stamp,'dd' ) as date1 , fsc.allocated_gas/28.31685 as monthly,  
      last_value(fsc.allocated_gas/28.31685 ignore nulls) 
         over(partition by name 
              order by trunc( fsc.date_stamp,'dd' )
              rows between unbounded preceding and 0 preceding) as daily_gas
 from fv_site_allocation_calc fsc  , fv_site fs 
                  where fsc.site_id = fs.site_id
                  and fs.name = 'Maz-02(L)'
                  AND fsc.date_stamp >= TO_DATE ('20080731', 'YYYYMMDD')
 order by   trunc( fsc.date_stamp,'dd' )
2

You are using function "to_date" - is this Oracle?

3

Yes using to_date the query belongs to oracle ... sorry when i submitted the question i realized i send oracle question.

I use before to get the last_day
(fsc.allocated_gas / 28.31685)/(EXTRACT(DAY FROM LAST_DAY(fs.date_stamp)))

But not know how to map with

last_value(fsc.allocated_gas/28.31685 ignore nulls)

4

This is Microsoft SQL Server forum, so there might not be anyone here familiar with Oracle and able to solve your problem. I would expect you would be more likely to get answers on an Oracle forum