Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2014
    Posts
    2

    Unanswered: Retrive the Prior date period and prior year end

    Hi All,

    I have a table in the database which has date columns Date_id, yr_num, month_num, week_num. The Date Id has the values 2013-01-04, 2013-01-11, 2013-01-18, 2013-01-25, 2013-01-31, 2012-12-31, 2012-12-30.... I am using Db2 (version 9.0) as the database. I have to retrieve the prior Period of Date, Prior Year End values. Below is the sample data in table. Thanks in Advance for suggestion.


    Date_ID Prior_date Prior_Year_end YR_num Mon_num
    2013-01-04 0 2012-12-31 2013 1
    2013-01-11 2013-01-04 2012-12-31 2013 1
    2013-01-18 2013-01-11 2012-12-31 2013 1
    2013-01-25 2013-01-18 2012-12-31 2013 1
    2013-01-31 2013-01-25 2012-12-31 2013 1


    1. Prior Period calculation. For the First period, it should give as zero (0) and for the remaining periods it should fetch the prior period.

    2. Prior Year End value (E.g. 2012-12-31 )



    Thanks in Advance.

  2. #2
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by Krithika331 View Post
    Hi All,

    I have a table in the database which has date columns Date_id, yr_num, month_num, week_num. The Date Id has the values 2013-01-04, 2013-01-11, 2013-01-18, 2013-01-25, 2013-01-31, 2012-12-31, 2012-12-30.... I am using Db2 (version 9.0) as the database. I have to retrieve the prior Period of Date, Prior Year End values. Below is the sample data in table. Thanks in Advance for suggestion.


    Date_ID Prior_date Prior_Year_end YR_num Mon_num
    2013-01-04 0 2012-12-31 2013 1
    2013-01-11 2013-01-04 2012-12-31 2013 1
    2013-01-18 2013-01-11 2012-12-31 2013 1
    2013-01-25 2013-01-18 2012-12-31 2013 1
    2013-01-31 2013-01-25 2012-12-31 2013 1


    1. Prior Period calculation. For the First period, it should give as zero (0) and for the remaining periods it should fetch the prior period.

    2. Prior Year End value (E.g. 2012-12-31 )



    Thanks in Advance.
    You can use the lag function, a.k.a:

    Code:
    with t(d) as (
        values date('2013-01-04'), '2013-01-11', '2013-01-18', '2013-01-25', '2013-01-31' 
    ) 
    select d, lag(d) over (order by d) from t
    
    D          2         
    ---------- ----------
    01/04/2013 -         
    01/11/2013 01/04/2013
    01/18/2013 01/11/2013
    01/25/2013 01/18/2013
    01/31/2013 01/25/2013
    Since 0 is not a date I don't understand how you are going to map that there
    --
    Lennart

Posting Permissions

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