1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oralce sql query to pick current and previous records

Discussion in 'SQL PL/SQL' started by surestce, Sep 1, 2014.

  1. surestce

    surestce Guest

    I have a table that contains all transactions with Quantity column. Need to calculate the cumulative total of quantity of transaction group and update in each transaction row.

    TRANSACTION table:

    id(PK) group_id, txn_id, txn_date, Quantity, cumulative_qty
    1 1 111 12-AUG-14 10 10
    2 1 112 13-AUG-14 -5 5
    3 1 113 14-AUG-14 2 7
    4 2 221 23-AUG-14 15 15
    5 2 222 23-AUG-14 15 30

    I wrote MERGE INTO script and it is working file. But the problem is that it is updating old records also. This table is having huge volume of data and that hit is performance issue.

    Now I need a merge script that need to fetch only unprocessed new records (cumulative_qty is NULL) and add the Quantity with last cumulative_qty value.

    Suppose, if there is any new transactions for the same group, the select of merge should pick up old and new records alone and update the new record.

    id(PK) group_id, txn_id, txn_date, Quantity, cumulative_qty
    6 1 114 25-AUG-14 5 NULL
    7 1 115 26-AUG-14 2 NULL
    8 2 223 25-AUG-14 -10 NULL

    merge into
    txn t1
    using (
    lag(cumulative_qty, 1) over (
    partition by group_id
    order by txn_id
    ) as prev_cum_qty
    ) x
    (t1.txn_id = x.txn_id)
    when matched then update set
    cumulative_qty = quantity + prev_cum_qty
    cumulative_qty is null;

    In the above merge query, the select query returns all 7 rows to process and then merge 2 rows. But I want the select query that should return only 4 rows (id-3,5,6,7,8) i.e. the last cumulative row (order by max date and id) and unprocessed rows only.

    I am using Oracle 11g. Please help me.
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Likes Received:
    Trophy Points:
    Russian Federation

    your query returns two rows(2 rows with nulls values in field cumulative_qty):

    !!! is necessary to provide a ddl-script here and dml-script!!!!

    What you need to receive as result?

    for example :
    Code (SQL):

    table1 AS
    -- select id group_id, txn_id, txn_date, Quantity, cumulative_qty
    SELECT 1 id, 1 group_id, 111 txn_id, to_date('12-AUG-14','dd-mon-rr') txn_date, 10 Quantity, 10 cumulative_qty FROM dual UNION ALL
    SELECT 2, 1, 112, to_date ('13-AUG-14','dd-mon-rr') ,-5 ,5 FROM dual UNION ALL
    SELECT 3, 1, 113, to_date ('14-AUG-14','dd-mon-rr'), 2, 7 FROM dual UNION ALL
    SELECT 4, 2, 221,to_date('23-AUG-14','dd-mon-rr'), 15 ,15 FROM dual UNION ALL
    SELECT 5, 2, 222, to_date('23-AUG-14','dd-mon-rr'), 15 ,30 FROM dual UNION ALL
    SELECT 6, 1, 114, to_date('25-AUG-14','dd-mon-rr'), 5, NULL FROM dual UNION ALL
    SELECT 7, 1, 115, to_date('26-AUG-14','dd-mon-rr'), 2 ,NULL FROM dual UNION ALL
    SELECT 8, 2, 223,to_date ('25-AUG-14','dd-mon-rr'), -10, NULL FROM dual

    ,ress AS
    SELECT id, group_id, txn_id, txn_date,Quantity,
           lag(cumulative_qty,1,0) OVER (partition BY group_id ORDER BY txn_date) AS new_cumulative_qty,
           ROW_NUMBER () OVER (partition BY group_id,decode(cumulative_qty,NULL,1,0) ORDER BY txn_date DESC) rn
    FROM table1 t1
     SELECT rn,
           id, group_id, txn_id, txn_date,Quantity,
           quantity+ lag(new_cumulative_qty,1,0) OVER (partition BY group_id ORDER BY txn_date) AS new_cumulative_qty
     FROM ress r
    WHERE  rn = 1