Generating history of record change

FLANDERS · #1 (**permalink**) 12-21-09, 09:50

Hi,
DB2 version 9.1 - im trying to develop a query to show the change history of an item using two tables, reserve and reservehistory. Initially a reserve is created with an amount and a date. If this reserve amount is changed, the reserve object is updated and a record created in the history table that is a copy of the original reserve date and amount. I want to have a result set record for each month that there was a change, including the cumulative amount change within that month.

e.g. A reserve is created in March 2009 with amount €10,000. Then its updated to €15000 in May, updated to €8000 in August and again to €7000 later in August. Hence in this case the tables would be

reservehistory table
amount reservedate reservePKey
€10,000 2009-03-15 1
€15,000 2009-05-15 1
€8,000 2009-08-02 1

reserve table
amount reservedate reservePKey
€7,000 2009-08-15 1
€6,000 2009-08-30 2

Here the result of the query would be

reservePKey amountChange amount Month
1 €10,000 €10000 2009-03 (the amounts are the same as this is first history)
1 €5,000 €15000 2009-05
1 -€8,000 €7000 2009-08 (the amountChange is cumulative change in month)
2 €6,000 €6000 2009-05 (no history, so just reserve makes up amounts)

I've managed to get the latest history change per month ok (including amountChange) ok but I cant figure out how to get the reserve into the result set.

This may not be very clear apologies......
Thanks

tonkuma · #2 (**permalink**) 12-21-09, 11:23

If its updated to €12000 in September,

reservehistory table would be:
amount reservedate reservePKey
€10,000 2009-03-15 1
€15,000 2009-05-15 1
€8,000 2009-08-02 1
€7,000 2009-08-15 1

and reserve table would be:
amount reservedate reservePKey
€12,000 2009-09-13 1
€6,000 2009-08-30 2

Is this right?

If yes, then what is the amount of 2009-08 in the result of the query?

tonkuma · #3 (**permalink**) 12-21-09, 11:29

Quote:

how to get the reserve into the result set

UNION reservehistory and reserve, then apply your query.

FLANDERS · #4 (**permalink**) 12-21-09, 11:53

Quote:

Originally Posted by tonkuma

If its updated to €12000 in September,

reservehistory table would be:
amount reservedate reservePKey
€10,000 2009-03-15 1
€15,000 2009-05-15 1
€8,000 2009-08-02 1
€7,000 2009-08-15 1

and reserve table would be:
amount reservedate reservePKey
€12,000 2009-09-13 1
€6,000 2009-08-30 2

Is this right?

If yes, then what is the amount of 2009-08 in the result of the query?

Yeah that is correct, the €7000 would become a history amount while the reserve is updated to the new amount, €15000, with September's date, as you have there. The amount for 2009-08 would remain the same as the change you have included was done in September - there would be a new entry for September with €12000 as the amount column while the amountChange would be difference between the amount and the last value in August = 12000 - 7000 = 5000

So, the result set would be:

reservePKey amountChange amount Month
1 €10,000 €10000 2009-03
1 €5,000 €15000 2009-05
1 -€8,000 €7000 2009-08
1 €5,000 €12000 2009-09
2 €6,000 €6000 2009-05

Does that make sense?

Yeah I was thinking union or union all, and know how they work, just not sure how to fit it in with the subtraction for the amountChange column, which is the awkward part(I think)?

thanks

Lenny77 · #5 (**permalink**) 12-21-09, 12:58

Quote:

Originally Posted by FLANDERS

Hi,
DB2 version 9.1 - im trying to develop a query to show the change history of an item using two tables, reserve and reservehistory. Initially a reserve is created with an amount and a date. If this reserve amount is changed, the reserve object is updated and a record created in the history table that is a copy of the original reserve date and amount. I want to have a result set record for each month that there was a change, including the cumulative amount change within that month.

e.g. A reserve is created in March 2009 with amount €10,000. Then its updated to €15000 in May, updated to €8000 in August and again to €7000 later in August. Hence in this case the tables would be

reservehistory table
amount reservedate reservePKey
€10,000 2009-03-15 1
€15,000 2009-05-15 1
€8,000 2009-08-02 1

reserve table
amount reservedate reservePKey
€7,000 2009-08-15 1
€6,000 2009-08-30 2

Here the result of the query would be

reservePKey amountChange amount Month
1 €10,000 €10000 2009-03 (the amounts are the same as this is first history)
1 €5,000 €15000 2009-05
1 -€8,000 €7000 2009-08 (the amountChange is cumulative change in month)
2 €6,000 €6000 2009-05 (no history, so just reserve makes up amounts)

I've managed to get the latest history change per month ok (including amountChange) ok but I cant figure out how to get the reserve into the result set.

This may not be very clear apologies......
Thanks

Maybe something like this ?

Code:

Select 
coalesce(st.reservePKey, en.reservePKey, 0) reservePKey, 
coalesce(st.amount, en.amount) amount, 
ifnull(st.amount, 0) - ifnull(en.amount, 0) Change_amount, 
coalesce(st.Month, en.Month, '0001-01')
From 
(select reservePKey, min(amount) amount, substr(char(reservedate), 1, 7) Month
from 
(select reservePKey, amount, reservedate 
from reservehistory
Union 
select reservePKey, amount, reservedate 
from reserve ) s1
group by 
s1.reservePKey, substr(char(s1.reservedate), 1, 7) ) st

Full Join 

(select reservePKey, max(amount) amount, substr(char(reservedate), 1, 7) Month
from 
(select reservePKey, amount, reservedate 
from reservehistory
Union 
select reservePKey, amount, reservedate 
from reserve ) e1
group by 
s1.reservePKey, substr(char(s1.reservedate), 1, 7) ) en

On st.reservePKey = en.reservePKey
   and st.Month  = en.Month

Lenny

Lenny77 · #6 (**permalink**) 12-21-09, 14:59

Could be shorter and faster with some intermediate table
(global temporary table, or common table expression):

Code:

with UniReserve as 
(
select reservePKey, amount, amount, substr(char(reservedate), 1, 7) Month
from
( 
select reservePKey, amount, reservedate 
from reservehistory
Union 
select reservePKey, amount, reservedate 
from reserve ) un
)
select 
coalesce(st.reservePKey, en.reservePKey, 0) reservePKey, 
coalesce(st.amount, en.amount) amount, 
ifnull(st.amount, 0) - ifnull(en.amount, 0) Change_amount, 
coalesce(st.Month, en.Month, '0001-01')
From
(select reservePKey, max(amount) amount, Month
From 
(select reservePKey, min(amount) amount, Month
from  UniReserve
group by 
reservePKey, Month ) s1 ) st
Full Join 
(select 
    reservePKey, max(amount) amount, substr(char(reservedate), 1, 7) Month
From 
(select 
    reservePKey, min(amount) amount, substr(char(reservedate), 1, 7) Month
from  UniReserve
group by 
reservePKey, Month ) e1 ) en
On st.reservePKey = en.reservePKey
   and st.Month  = en.Month

Lenny

FLANDERS · #7 (**permalink**) 12-21-09, 17:50

Hi Lenny,
Thanks for that but for the example figures does that give the following (incorrect) result set:

Code:

reservePKey 	amountChange 	amount 		Month
1		      0		10000		2009-03	
1		      0		15000		2009-05
1		      1000	8000	        2009-08
1		      0		12000		2009-09
2		      0		6000	        2009-08

The expected result set is

Code:

reservePKey 	amountChange 	amount 		Month
1		      10000	10000		2009-03	
1		      10000	15000		2009-05
1		      -8000	7000	        2009-08
1		      5000	12000		2009-09
2		      6000	6000	        2009-08

The amountChange needs to be calculated subtracting the last entry in a month from the first entry in the next month (whereas I think yours is subtracting the biggest amount from the smallest amount, within the same month). It might be worth noting that if there is no reserve then no history can exist, but a reserve can exist with no history (i.e. created once and never changed).

Does anyone have any further insights?

tonkuma · #8 (**permalink**) 12-21-09, 20:44

How about this?

Data:
(I added some more updates in September.)

Code:

------------------------------ Commands Entered ------------------------------
WITH
/*****************************************
********** Begin of sample data **********
*****************************************/
 reservehistory(amount, reservedate, reservePKey) AS (
VALUES
  (10000, '2009-03-15', 1)
, (15000, '2009-05-15', 1)
, ( 8000, '2009-08-02', 1)
, ( 7000, '2009-08-15', 1)
, (12000, '2009-09-04', 1)
, ( 4000, '2009-09-18', 1)
)
,reserve(amount, reservedate, reservePKey) AS ( 
VALUES
  ( 9000, '2009-09-22', 1)
, ( 6000, '2009-08-30', 2)
)
/*****************************************
**********   End of sample data **********
*****************************************/

Query:
Changed: LAG was not supported on DB2 9.1 for LUW.

Code:

SELECT reservePKey
     , amount
       - COALESCE(
            MAX( amount )
               OVER( PARTITION BY reservePKey
                         ORDER BY reservedate
                     ROWS BETWEEN 1 PRECEDING
                              AND 1 PRECEDING )
          , 0 )  AS amountChange
     , amount
     , RTRIM( CHAR( YEAR (reservedate) ) ) || '-'
       || SUBSTR( DIGITS( MONTH(reservedate) ) , 9, 2 ) AS Year_Month
  FROM (SELECT u.*
             , ROW_NUMBER()
                  OVER( PARTITION BY reservePKey
                                   , YEAR (reservedate)
                                   , MONTH(reservedate)
                            ORDER BY reservedate DESC ) AS rn
          FROM (SELECT * FROM reservehistory
                UNION ALL
                SELECT * FROM reserve
               ) u
       ) s
 WHERE rn = 1
 ORDER BY
       reservePKey
     , Year_Month
;

Result:

Code:

------------------------------------------------------------------------------

RESERVEPKEY AMOUNTCHANGE AMOUNT      YEAR_MONTH    
----------- ------------ ----------- --------------
          1        10000       10000 2009-03       
          1         5000       15000 2009-05       
          1        -8000        7000 2009-08       
          1         2000        9000 2009-09       
          2         6000        6000 2009-08       

  5 record(s) selected.

tonkuma · #9 (**permalink**) 12-21-09, 20:55

I calculated as....
amount: the value of last row of a month.
amountChange: ( the value of last row of a month) - ( the value of last row of last month)

Is this right?

FLANDERS · #10 (**permalink**) 12-22-09, 11:12

Wow thanks! That result set is perfect, now to figure out how or what its doing - never seen row_number(), over partition etc. before! (I was trying to do it with "normal" SQL) - are these functions DB portable?

How do you think this would this be performance-wise if the tables were large, since the query effectively does a union of the two?

Thanks again, its given me a great point in the right direction!

Internet Services

Web Development

Digital Marketing

Consumer Tech