Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2009
    Posts
    37

    Unanswered: Generating history of record change

    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

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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?

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    how to get the reserve into the result set
    UNION reservehistory and reserve, then apply your query.

  4. #4
    Join Date
    Nov 2009
    Posts
    37
    Quote Originally Posted by tonkuma View Post
    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

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb Something like this ?

    Quote Originally Posted by FLANDERS View Post
    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

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb Could be shorter with intermediate table

    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
    Last edited by Lenny77; 12-21-09 at 16:05.

  7. #7
    Join Date
    Nov 2009
    Posts
    37
    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?

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 12-21-09 at 22:27.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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?

  10. #10
    Join Date
    Nov 2009
    Posts
    37
    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!

Posting Permissions

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