# Thread: Issue Calculating MTD value in DB2 query

1. Registered User
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. Registered User
Join Date
Oct 2007
Posts
161
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. Registered User
Join Date
Jul 2016
Location
Moscow
Posts
294
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```

#### Posting Permissions

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