Running Total from minimum to current month

user_1986823 0
Viewed 76

I have two tables one with Purchase ID with month of amount purchased. Another one with calendar. I need to find the running total to the current month with the missing months. I tried the below query but I am not getting correct result. Not sure if its possible in a singe query . Can you please suggest.

Input : Sales

Input

Input: Calendar

Calendar Table

I need to find the running total like below.

Output:

Output : Running Total with missing months

QUERY Tried

  SELECT PID,b.CALMONTH,SUM("AMOUNT") OVER(PARTITION BY "PID" ORDER BY a."YEAR_MONTH") running_total
  FROM SALES a RIGHT JOIN (SELECT YEAR_MONTH  CALMONTH FROM CALENDAR
                           WHERE  CALMONTH BETWEEN '201901' and TO_CHAR(CURRENT_TIMESTAMP,'YYYYMM')) b
  ON a.CALMONTH=b.CALMONTH
oracle11g oracle sql
3 Answers

I would recommend cross joining the pids with the months to generate the rows. Then use a left join to bring in the data and a cumulative sum.

It is actually pretty simple:

select p.pid, c.year_month,
       sum(s.amount) over (partition by p.pid order by c.year_month) as running_amount
from (select pid, min(year_month) as min_year_month
      from sales
      group by pid
     ) p cross join
     calendar c left join
     sales s
     on s.pid = p.pid and s.year_month = c.year_month   
where c.year_month >= p.min_year_month 
order by p.pid, c.year_month;
user_11440350

You can use below query to get your desired result. Good luck.

with SalesData as(
select t.pid,t.year_month,sales.amount from 
(select c.year_month,ps.pid from calendar c,
(select distinct pid from sales)ps)t 
left join sales  on t.pid=sales.pid and t.year_month=sales.year_month 
), results as (
SELECT pid,to_date(year_month,'YYYY-MM')as yearmonth, SUM(amount)  
OVER( partition by pid ORDER BY to_date(year_month,'YYYY-MM') 
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) as amount
FROM SalesData )

select * from results where amount>0 order by pid,yearmonth

enter image description here

user_86516010

If you already have a calendar table, and you want (or must) use it, you can take advantage of a "data densification" feature of joins, known as "partitioned outer join", as shown below.

with
  prep (pid, year_month, cumulative_amount) as (
    select s.pid, year_month, 
           sum(s.amount) over (partition by s.pid order by year_month)
    from   calendar c left join sales s partition by (pid) using (year_month)
  )
select pid, year_month, cumulative_amount
from   prep
where  cumulative_amount is not null
order  by pid, year_month
;

PID YEAR_MONTH CUMULATIVE_AMOUNT
--- ---------- -----------------
  1 202010                   100
  1 202011                   100
  1 202012                   150
  1 202101                   150
  2 202011                    75
  2 202012                    75
  2 202101                   125

Notice the parition by (pid) clause right after the left join clause. What it means is that the sales table is partitioned into separate (and disjoint) sub-tables, one for each distinct pid value; the "left join" is performed for each of these sub-tables separately, then the results are put together with the logical equivalend of union all.

The rest is standard - use the analytic sum function for cumulative amounts, and in an outer query filter out the rows before the "first YM" for each pid - you can identify those trivially since they will have null cumulative amount.

Admin1
Related