Abhishek Abhishek - 1 month ago 24
SQL Question

Loop in Oracle SQL, comparing one month to another

I have to draft a sql qery which does the following:

Compare current week (e.g. week 10) amount to the average amount over previous 4 weeks (Week# 9,8,7,6).
Now I need to run the query on a monthly basis so say for weeks (10,11,12,13).
As of now I am running it four times giving the week parameter on each run.

For example my current query is something like this :

select account_id, curr.amount,hist.AVG_Amt
from
(
select
to_char(run_date,'IW') Week_ID,
sum(amount) Amount,
account_id
from Transaction t
where to_char(run_date,'IW') = '10'
group by account_id,to_char(run_date,'IW')
) curr,
(
select account_id,
sum(amount) / count(to_char(run_date,'IW')) as AVG_Amt
from Transactions
where to_char(run_date,'IW') in ('6','7','8','9')
group by account_id
) hist
where
hist.account_id = curr.account_id
and curr.amount > 2*hist.AVG_Amt;


As you can see, if I have to run the above query for week 11,12,13 I have to run it three separate times. Is there a way to consolidate or structure the query such that I only run once and I get the comparison data all together?

Just an additional info, I need to export the data to Excel (which I do after running query on the PL/SQL developer) and export to Excel.

Thanks!

-Abhi

vkp vkp
Answer

You can use a correlated sub-query to get the sum of amounts for the last 4 weeks for a given week.

select 
    to_char(run_date,'IW') Week_ID,
    sum(amount) curAmount,
    (select sum(amount)/4.0 from transaction 
     where account_id = t.account_id
     and to_char(run_date,'IW') between to_char(t.run_date,'IW')-4 
                                        and to_char(t.run_date,'IW')-1
    ) hist_amount,
    account_id
from Transaction t
where to_char(run_date,'IW') in ('10','11','12','13')
group by account_id,to_char(run_date,'IW')

Edit: Based on OP's comment on the performance of the query above, this can also be accomplished using lag to get the previous row's value. Count of number of records present in the last 4 weeks can be achieved using a case expression.

with sum_amounts as 
(select to_char(run_date,'IW') wk, sum(amount) amount, account_id 
 from Transaction
 group by account_id, to_char(run_date,'IW')
)
select wk, account_id, amount, 
1.0 * (lag(amount,1,0) over (order by wk) + lag(amount,2,0) over (order by wk) +
       lag(amount,3,0) over (order by wk) + lag(amount,4,0) over (order by wk)) 
/ case when lag(amount,1,0) over (order by wk) <> 0 then 1 else 0 end +
  case when lag(amount,2,0) over (order by wk) <> 0 then 1 else 0 end +
  case when lag(amount,3,0) over (order by wk) <> 0 then 1 else 0 end +
  case when lag(amount,4,0) over (order by wk) <> 0 then 1 else 0 end 
as hist_avg_amount 
from sum_amounts