Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2017
    Posts
    2

    Unanswered: Issue Calculating MTD value in DB2 query

    Hi, I have a requirement to perform product of previous rows with current row dynamically every day to calculate the percentage change in pricing value for MTD. For this i have a formula shared which does a product of all daily price percentage each day and calculates MTD. (So Column B is the output format of the value required). ie. Need to multiply each value from Column A daily with all previous values till month start . Basically Need output in below format:


    Column(Date) ColumnA(Price) ColumnB(product)
    D1 P1 P1
    D2 P2 P1*P2
    D3 P3 P1*P2*P3 or (B2*P3)

    and so on...

    Trying to do self joins and not getting desired output, Any help with query in DB2 is appreciated much ! - Thanks Any quick help is appreciated

  2. #2
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    take a look at using recursion with a CTE. In fact, there should be several examples in the history of this forum that you can find via google

  3. #3
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Hi,

    Try this:
    Code:
    with t (d, a) as (values
      (date('2017-01-01'), 1)
    , (date('2017-01-02'), 2)
    , (date('2017-01-03'), 3)
    
    , (date('2017-02-01'), 4)
    , (date('2017-02-02'), 5)
    , (date('2017-02-03'), 6)
    )
    select d, a
    , power(10, sum(log10(a)) over (
    partition by sum(case when int(d)/100=int(coalesce(lag(d, 1) over (order by d), d))/100 then 0 else 1 end) over (order by d)
    order by d)
    ) b
    from t;
    
    D               A         B
    ----------     --        --
    2017-01-01	1	  1
    2017-01-02	2	  2
    2017-01-03	3	  6
    2017-02-01	4	  4
    2017-02-02	5	 20
    2017-02-03	6	120
    Regards,
    Mark.

Posting Permissions

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