var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
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....
Use ROW_NUMBER in a subquery, like...
... fetch one record based on two columns and the corresponding nearest future date and time,
ROW_NUMBER() OVER(PARTITION BY cola , colb ORDEER BY date_time) AS r_num
WHERE r_num = 1 in final(outmost) select.
Specify date_time > current_timestamp in WHERE clause in the subquery.
... greater than the current date.
If the date and time were different columns,
use TIMESTAMP(date , time) instead of date_time.
Last edited by tonkuma; 11-20-13 at
Reason: Replace current_date by current_timestamp.
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