CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Mani
Mani

Reputation: 741

Cumulative data along with the original data

I have two tables .

Input:

enter image description here

I have joined with the calendar table and bring the data till current.

enter image description here

I need a output .

enter image description here

I tried a query with UNION and Aggregation but I need to query two times and aggregate the same table . Since the table is very big .Is there a option to do different way

SELECT ID ,PERIOD,SUM(AMOUNTYTD) AMOUNTYTD,SUM(AMOUNT) AMOUNT
FROM (
            SELECT ID ,b.PERIOD,SUM(AMOUNT) AMOUNTYTD,0 AMOUNT
            FROM transaction a RIGHT OUTER JOIN CALENDAR b
            ON b.PERIOD<=a.PERIOD
            UNION ALL
            SELECT ID ,PERIOD,0,SUM(AMOUNT)
            FROM transaction
            GROUP BY ID,PERIOD
    )
GROUP BY ID,PERIOD

Upvotes: 1

Views: 87

Answers (2)

Popeye
Popeye

Reputation: 35930

You have the query - I have joined with the calendar table and bring the data till current. let us assume it as your_query

You can use analytical function on it as follows:

Select t.*,
       Case when lead(amountytd) over (partition by id order by period) = amountytd 
            then null 
            else amountytd
       end as amount
  From (your_query) t

Upvotes: 0

user5683823
user5683823

Reputation:

Showing the periodic amount side by side with the cumulative amount is easy - actually you only need to be able to create the correct table with the periodic amounts, the cumulative amounts are a simple application of analytic sum.

The key to joining the calendar table to the "input" data is to use a partitioned outer join - notice the partition by (id) clause in the join of the two tables. This causes the "inputs" data to be partitioned into separate sub-tables, one for each distinct id; the outer join to the calendar table is done separately for each such sub-table, and then the results are combined with a logical "union all".

with
  input (id, period, amount) as (
    select 1, 202010, 100 from dual union all
    select 1, 202011,  50 from dual union all
    select 2, 202011, 400 from dual
  )
, calendar (period) as (
    select 202010 from dual union all
    select 202011 from dual union all
    select 202012 from dual union all
    select 202101 from dual
  )
select id, period, amountytd, amount
from   (
         select i.id, period, i.amount, 
                sum(i.amount) over (partition by i.id order by period)
                  as amountytd
         from   calendar c left outer join input i partition by (id)
                           using (period)
       )
where  amountytd is not null
order  by id, period
;

 ID     PERIOD  AMOUNTYTD     AMOUNT
--- ---------- ---------- ----------
  1     202010        100        100
  1     202011        150         50
  1     202012        150           
  1     202101        150           
  2     202011        400        400
  2     202012        400           
  2     202101        400

Upvotes: 3

Related Questions