Find the Nearest Future Record in a table

Database
Enthusiast

Find the Nearest Future Record in a table

Hello,

I have table from which I need to derive the nearest future record and populate it in the subsequent table as a desired output.

Attaching the image of the scenario of what the table looks like and what is the desired output. Basically I have NULLS populated in one of my columns MY_ID that I need to populate with the nearest future record based on the date.

Thank You

3 REPLIES
Teradata Employee

Re: Find the Nearest Future Record in a table

select A.emp_id, A.dep_id, A.src_cd, A.strt_date, A.end_date, first_value(B.my_id) over( partition by A.emp_id, A.dep_id, A.src_cd, A.strt_date order by B.strt_date asc ) as my_id
from mytable A
left outer join mytable B
on A.emp_id = B.emp_id
and A.dep_id = B.dep_id
and A.src_cd = B.src_cd
and A.strt_date <= B.strt_date
and B.my_id is not null
qualify row_number() over ( partition by A.emp_id, A.dep_id, A.src_cd, A.strt_date order by B.strt_date asc ) = 1
Junior Contributor

Re: Find the Nearest Future Record in a table

TD14.10+ supports Standard SQL's FIRST/LAST_VALUE:

first_value(my_id ignore nulls)
over (partition by emp_id
order by strt_dt
rows between current row and unbounded folowing)
Enthusiast

Re: Find the Nearest Future Record in a table

Thank You for the responses.

I had to tweak the query little bit may be because of different key columns in the environment I am in but the solutions provided worked perfectly fine.