Results 1 to 7 of 7

Thread: Date function

  1. #1
    Join Date
    Nov 2011
    Location
    Chennai
    Posts
    27

    Unanswered: Date function

    Hi ,

    how to compare the yesterdays values and the todays in a table based on the inserted date. the output should show the difference alone. i have a separate column for updated date but i should not use (current date - 1 days) to get the yesterday's values

    Pls find the description of that table.

    Data type Column
    Column name schema Data type name Length Scale Nulls
    ------------------------------- --------- ------------------- ---------- ----- ------
    NAME SYSIBM VARCHAR 32 0 Yes
    VALUE SYSIBM VARCHAR 256 0 Yes
    VALUE_FLAGS SYSIBM VARCHAR 10 0 Yes
    DEFERRED_VALUE SYSIBM VARCHAR 256 0 Yes
    DEFERRED_VALUE_FLAGS SYSIBM VARCHAR 10 0 Yes
    DATATYPE SYSIBM VARCHAR 128 0 Yes
    DBPARTITIONNUM SYSIBM SMALLINT 2 0 Yes
    UPDATED_DATE SYSIBM DATE 4 0 Yes

    any help is appreciated

    Thanks
    TJ
    Last edited by thawfii88; 01-06-12 at 06:08. Reason: chumma

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please publish sample/test data and expected output from the sample/test data
    with one of the following format
    (1) CREATE TABLE statement and INSERT statement.
    or
    (2) WITH common-table-expression.

  3. #3
    Join Date
    Nov 2011
    Location
    Chennai
    Posts
    27
    NAME VALUE UPDATED_DATE
    ---- ----- ---------------

    avg_appl 5 1/06/2012

    app_ctl_heap_sz 16384 1/06/2012

    avg_appl 3 1/07/2012

    app_ctl_heap_sz 16384 1/07/2012



    we have to compare the rows based on the updated_date (between yesterday and today) and return the yesterday's value incase of any change.


    avg_appl 5 1/06/2012

    note : ther are nearly 800 rows updated daily it has to compare all the 800 rows of yesterday and today


    by TJ

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    A solution may be to use LEAD OLAP specification.

  5. #5
    Join Date
    Nov 2011
    Location
    Chennai
    Posts
    27
    any other way ?

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    any other way ?
    Why?
    Was there any issue on LEAD OLAP specification?

    Please see Information Center about OLAP specifications including LEAD
    OLAP specifications - IBM DB2 9.7 for Linux, UNIX, and Windows

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Could you make a required query?

    If you published sample/test data with the format I hoped, I could show you an example.
    Quote Originally Posted by tonkuma View Post
    Please publish sample/test data and expected output from the sample/test data
    with one of the following format
    (1) CREATE TABLE statement and INSERT statement.
    or
    (2) WITH common-table-expression.

Posting Permissions

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