Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2017
    Posts
    9

    Unanswered: SQL to calculate sum variance of a column value from previous date to current date

    I have table1 and table2 as follows:

    table1 columns: tr_key, PROC_DATE and MTM_VALUE and more...

    table2 columns: tr_key, APP_ID and more .... tr_key is the common column between the two tables to join.

    None of the above mentioned columns are key primary key.

    My requirement is build a sql to determine the sum difference of a column (MTM_VALUE) of 1st max date (proc_date) and 2nd max date(proc_date). There are multiple rows for each proc_date.


    Table1
    Proc_date MTM_Value Tr_key
    2017-08-14 10 KeyValue
    2017-08-14 25 KeyValue
    2017-08-14 20 KeyValue
    2017-08-14 15 KeyValue
    2017-08-11 100 KeyValue
    2017-08-11 150 KeyValue
    2017-08-11 200 KeyValue
    2017-08-11 100 KeyValue




    Table2
    APP_ID TR_KEY
    ABC keyvalue
    XYZ keyvalue
    PRQ keyvalue


    Expected RESULT of SQL:
    ===============
    Proc_date sum(MTM_Value) Percent Variance
    2017-08-14 70 (70-550) * 100 / 550
    2017-08-11 550 (550 - xxx) * 100 / xxx <== xxx indicates values for next proc_date
    Last edited by srinivas_sp; 08-15-17 at 15:39.

  2. #2
    Join Date
    May 2010
    Location
    India
    Posts
    90
    Provided Answers: 2
    You can use lag function. See the link for more details.

    https://www.ibm.com/support/knowledg.../r0023461.html


    Satya...

  3. #3
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    282
    Provided Answers: 41
    Try this:
    Code:
    select 
      proc_date
    , mtm_value, mtm_value_prev
    , 100 * (mtm_value - mtm_value_prev) / nullif (mtm_value_prev, 0) Percent_Variance
    from (
    select 
      proc_date
    , sum(mtm_value) mtm_value
    , lag(sum(mtm_value), 1) over (order by proc_date) mtm_value_prev
    from table(values
      ('2017-08-14', 10)
    , ('2017-08-14', 25)
    , ('2017-08-14', 20)
    , ('2017-08-14', 15)
    , ('2017-08-11', 100)
    , ('2017-08-11', 150)
    , ('2017-08-11', 200)
    , ('2017-08-11', 100)
    ) t (Proc_date, MTM_Value)
    group by proc_date
    )
    order by proc_date
    Regards,
    Mark.

  4. #4
    Join Date
    Aug 2017
    Posts
    9

    sql that I came up.

    first of all thanks to Mark and Satya.

    Below is the sql I have come up and submitted to my project and it's yet to be reviewed by the team. I am still thinking if I can further simply it after Mark's sql.

    //Percent variance of MTM values that tied with today's proc_date and previous day's proc_date.
    select ((t1.value1 - t2.value2)*100)/t2.value2 as percent1 from (
    //Select MTM value for first max proc_date(today's proc_date)
    (Select sum(FINAL_MTM_VALUE) as VALUE1 from PCI.CM_Metrics tm, PCI.CM_TRADE tr WHERE tm.proc_date = (SELECT MAX(proc_date) FROM PCI.CM_Metrics s2) and tr. app_id = 'FINDUR' and tm.cm_trade_key = tr.cm_trade_key ) as t1
    cross join
    //Select MTM value for second max proc_date (previous proc_date)
    (select sum(cast(FINAL_MTM_VALUE as INT)) as VALUE2 from PCI.CM_Metrics as tm, PCI.CM_TRADE tr WHERE proc_date = ( SELECT MAX(proc_date) from PCI. CM_Metrics WHERE proc_date < ( SELECT MAX(proc_date) from PCI.CM_Metrics )) and tr.app_id = 'XYZ' and tm.cm_trade_key = tr.cm_trade_key ) as t2
    )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •