simpleMan
simpleMan

Reputation: 65

How to calculate with two date range (Partition by and PRECEDING)

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

Answers (1)

simpleMan
simpleMan

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

Related Questions