Reputation: 65
I would like to calculate and compare the actual quarterly costs with the previous quarterly costs with the following formula: (actual Year avg quarterly cost) / (last Year avg quarterly cost)
I'm already done with the actual quarters:
avg ("TOTAL_MONTHLY_COST_IN_USD") OVER (PARTITION BY START_YEARS,
START_QUARTERS ORDER BY "START_QUARTERS" desc) as "PAST_Q"
But the hardest thing is to get the actual year -1 avg cost quarterly. In the final result I wish my table would look like this:
ACT_Q | PAST_Q | DIVIDE
567.2 | 456.6 | 1.24
In order to get PAST_Q I tried to use partition by and PRECEDING. I've the following support columns for calculations:
START_DATE , START_MONTH, START_QUARTER , START_YEAR .
Furthermore I created these with add_month(x, -12) and called them LAST_YEAR, etc..
My first attempt: I separated sum_cost calculation and count_rows in a inner select and then divided them, but in the result I got only 0 values.
case when nvl(quarter_count, 0)<>0 then
sum_totalcost/quarter_count
end as "PAST_AVG_COST_QUARTERLY"
(avg ("TOTAL_MONTHLY_COST_IN_USD") OVER (PARTITION BY START_YEARS, START_QUARTERS ORDER BY ACTUAL_YEAR_QUARTER RANGE BETWEEN 450 PRECEDING AND 0 FOLLOWING) - avg ("TOTAL_MONTHLY_COST_IN_USD") OVER (PARTITION BY START_YEARS, START_QUARTERS ORDER BY ACTUAL_YEAR_QUARTER RANGE BETWEEN 360 PRECEDING AND 0 FOLLOWING)) as sum_cost
(count (*) OVER (PARTITION BY START_YEARS,
START_QUARTERS ORDER BY
ACTUAL_YEAR_QUARTER RANGE BETWEEN 450 PRECEDING AND 0 FOLLOWING) - count (*) OVER
(PARTITION BY START_YEARS, START_QUARTERS
ORDER BY ACTUAL_YEAR_QUARTER RANGE BETWEEN 360 PRECEDING AND 0 FOLLOWING)) as count_rows
Anyone knows any best practice how to calculate this range? It would be really helpful! Thanks in advance.
Upvotes: 0
Views: 331
Reputation: 65
Luckily I solved the issue, by creating 2 tables with the "WITH" statement. The table contains the actual avg costs / quarter. Both of the tables have these data: act_YEAR | act_QUARTER | past_YEAR | past_QUARTER |COUNTRY | MONTHLY_COST then I joined the 1st table past year & quarter with the second table act year & quarter.
Upvotes: 1