Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2013
    Posts
    2

    Unanswered: Need Help with DB2 query

    I am trying to fetch one record based on two columns and the corresponding nearest future date and time, greater than the current date. I will try to explain it using one example:

    COLA COLB DATE TIME
    ----- ------ --------- --------
    12345 ABCD 2013-12-02 07:30:00
    12345 BCDE 2013-12-05 00:00:00
    12345 ABCD 2013-12-10 00:00:00
    12345 BCDE 2013-12-12 18:00:00
    23456 CDEF 2014-01-12 14:00:00
    23456 ABCD 2013-12-20 00:00:00
    23456 DEFG 2013-12-16 12:00:00
    23456 ABCD 2013-11-30 18:00:00

    Now for the combination of 12345 and ABCD only record with DATE/TIME 2013-12-02/07:30:00 should be fetched; For 12345 and BCDE, 2013-12-05/00:00:00 should be fetched; For 23456 and ABCD, 2013-11-30/00:00:00 should be fetched and so on.... I need to run the query against the entire database and write it in a file. Please help....

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... fetch one record based on two columns and the corresponding nearest future date and time,
    Use ROW_NUMBER in a subquery, like...
    ROW_NUMBER() OVER(PARTITION BY cola , colb ORDEER BY date_time) AS r_num
    and specify
    WHERE r_num = 1 in final(outmost) select.

    ... greater than the current date.
    Specify date_time > current_timestamp in WHERE clause in the subquery.

    If the date and time were different columns,
    use TIMESTAMP(date , time) instead of date_time.
    Last edited by tonkuma; 11-20-13 at 01:52. Reason: Replace current_date by current_timestamp.

  3. #3
    Join Date
    Nov 2013
    Posts
    2
    Thanks all for your help. I used the TIMESTAMP concept and then min of that. Here is the syntax for reference:

    MIN(CAST(<<DATE>> AS TIMESTAMP(0)) +(<<TIME>> - TIME '00:00:00' HOUR TO SECOND)) as SCHD_TS

    It's working with one

Posting Permissions

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