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!

How to Calculate a new fieild based on another column running difference .,

Discussion in 'SQL PL/SQL' started by asita, Sep 9, 2014.

  1. asita

    asita Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hello Good Morning,


    Could you please help me out to calculate a column value and insert into a table via oracle procedure



    First How to Find ta new column value (Bal Fee)



    Create table MyTable

    (Ord_NO numeric(10),
    CL_FEE numeric,
    CL_Date Date,
    SeqNum int
    )


    insert into MyTable values (1001,4500,'1/1/2014',45)
    insert into MyTable values (1001,300,'1/14/2014',95)
    insert into MyTable values (1001,0,'2/12/2014',22)

    insert into MyTable values (1002,700,'1/1/2014',145)
    insert into MyTable values (1002,250,'1/14/2014',395)
    insert into MyTable values (1002,100,'2/12/2014',252)

    insert into MyTable values (1003,0,'4/1/2013',65)
    insert into MyTable values (1003,100,'5/14/2014',95)
    insert into MyTable values (1003,20,'6/12/2014',131)

    insert into MyTable values (1004,0,'6/1/2014',15)
    insert into MyTable values (1004,25,'8/14/2014',17)

    insert into MyTable values (1005,500,'10/10/2014',15)

    select * from MyTable order by Ord_NO


    select ord_no, Seqnum, cl_fee , Cl_date
    from MyTable
    order by Ord_NO , CL_Date

    need to calculate a new column called BalFee which is group by ordno sort by ordno, CL_date then
    first column value for cl_fee will be zero
    then second column follow will be first Cl_fee - second Cl_fee
    then third column follow will be second Cl_fee - third Cl_fee so onnn,

    if ord_num has only one row then the balfee will be just 0

    if ord_num has two rows then first column value for clien_fee will be zero
    then second column follow will be first Cl_fee - second Cl_fee


    expected outcome will be

    Create table MyTableOutCome

    (Ord_NO numeric(10),
    CL_FEE numeric,
    CL_Date Date,
    SeqNum int,
    BalFee int
    )


    insert into MyTableOutCome values (1001,4500,'1/1/2014',45,0) -- First value so default to 0
    insert into MyTableOutCome values (1001,300,'1/14/2014',95,4200) -- Second value first - second 4500 - 300
    insert into MyTableOutCome values (1001,0,'2/12/2014',22,300) -- Third Value second - third so 300 - 0

    insert into MyTableOutCome values (1002,700,'1/1/2014',145,0)
    insert into MyTableOutCome values (1002,250,'1/14/2014',395,450)
    insert into MyTableOutCome values (1002,100,'2/12/2014',252,150)

    insert into MyTableOutCome values (1003,0,'4/1/2013',65,0)
    insert into MyTableOutCome values (1003,100,'5/14/2014',95,-100)
    insert into MyTableOutCome values (1003,20,'6/12/2014',131,80)

    insert into MyTableOutCome values (1004,0,'6/1/2014',15,0)
    insert into MyTableOutCome values (1004,25,'8/14/2014',17,-25)

    insert into MyTableOutCome values (1005,500,'10/10/2014',15,0)

    select * from MyTableOutCome order by Ord_NO , CL_Date



    also one more question how to do it in a proc by using "where Current of Cursor_Name" please



    Thank you very much in Advance

    Asita
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Asita,

    This is a duplicate post... I've just replied to the other one. :)