Get Data for every weeks average of high and low prices

Question

I have table named as

FP_BASIC_BD

whose structure is like as follows.

Name           Null?    Type         
-------------- -------- ------------ 
FS_ID          NOT NULL VARCHAR2(20) 
DATE           NOT NULL DATE         
CURRENCY                CHAR(3)      
PRICE                 FLOAT(126)   
PRICE_OPEN            FLOAT(126)   
PRICE_HIGH            FLOAT(126)   
PRICE_LOW             FLOAT(126)   
VOLUME                FLOAT(126)   

For any value of FS_ID I would like to calculate

weekends date
week start date
average(High of PRICE_HIGH+Low of PRICE_LOW)

for all the weeks.

Weekend is considered as Friday IF Fridays data not available then try to get 1 or any day before but after or equal to Monday of that week.

Week Start is considered as Monday. If Mondays data not available then get 1 or any day ahead but less or equal to to the weekend in above step.

Task of getting weekend and week start dates can be done in different query. But i want to use it as a range in a single query and get the required average.

Answer 1

First, please don't use Oracle keywords (e.g. date) as column names. Second, your description of how you want to calculate the average is ambiguous, so I included a couple of options.

I think this should work to get your week start / end dates.

select
    trunc("DATE", 'IW') as week,
    min(trunc("DATE")) as week_start,
    max(trunc("DATE")) as week_end,
    (max(price_high) + min(price_low)) / 2 as avg_price_weekly,
    avg(price_high+price_low) as avg_price_daily
from fp_basic_bd
where to_char("DATE", 'DY') not in ('SAT','SUN')
group by trunc("DATE", 'IW');

From your description, I'm guessing you don't have any Saturday/Sunday dates in this table. If you do, and you specifically want to exclude them from this query, let me know and I'll update my answer.

Edit: updated to exclude weekend days (sat/sun).