CODEWITHSUNDEEP
sqloracle-database
DaMonkeyMan123
DaMonkeyMan123

Reputation: 15

Average price based on dates

I am trying to calculate a weekly average, and add it as a new column along side what I already have. I have added week starting dates and week ending dates as I thought that would be useful (and how I found do it in a more familiar MS Excel environment). 

SELECT 
settlementdate as Trading_Interval,
rrp as Trading_Interval_QLD_RRP, 
NEXT_DAY(TRUNC(settlementdate-7),'MONDAY')AS Week_Starting, 
NEXT_DAY(TRUNC(settlementdate),'SUNDAY')AS Week_Ending
FROM fullauth.tradingprice
WHERE settlementdate > to_date('31/12/2019','dd/mm/yyyy') 
AND settlementdate <= to_date('01/01/2020','dd/mm/yyyy')
AND regionid = 'QLD1'
ORDER BY settlementdate DESC;

This code returns a table with 4 columns. I want to find the average price of the Trading_Interval_QLD_RRP when the Trading_Interval falls between the Week_Starting and Week_Ending dates and insert/create this weekly price as a new column

Thanks!

Upvotes: 0

Views: 36

Answers (1)

Popeye
Popeye

Reputation: 35900

You can use AVG as an analytical function as following:

SELECT 
settlementdate as Trading_Interval,
rrp as Trading_Interval_QLD_RRP, 
NEXT_DAY(TRUNC(settlementdate-7),'MONDAY')AS Week_Starting, 
NEXT_DAY(TRUNC(settlementdate),'SUNDAY')AS Week_Ending,
-- required changes as following
AVG(Trading_Interval_QLD_RRP) OVER (PARTITION BY TRUNC(settlementdate,'IW')) AS AVG_RRP 
FROM fullauth.tradingprice
WHERE settlementdate > to_date('31/12/2019','dd/mm/yyyy') 
AND settlementdate <= to_date('01/01/2020','dd/mm/yyyy')
AND regionid = 'QLD1'
ORDER BY settlementdate DESC;

Also, Note that TRUNC(settlementdate,'IW') and NEXT_DAY(TRUNC(settlementdate-7),'MONDAY') are same.

Cheers!!

Upvotes: 1

Related Questions