SQL Query, take last row to fill new column

Question

I have a table that looks like this:

id	company_id	from_dt
111	345	200601
112	345	200603
112	379	200703
113	338	200808
113	309	200912
113	391	201009
114	318	200910

And I want my table to look like this:

id	company_id	from_dt	to_dt
111	345	200601	NULL
112	345	200603	200703
112	379	200703	NULL
113	338	200808	200912
113	309	200912	201009
113	391	201009	NULL
114	318	200910	NULL

I just can't quite figure out the best way to format an SQL query to do this.

Answer 1

try this:

SELECT 
    *,
    LAG(from_dt, 1, NULL) OVER(PARTITION BY id ORDER BY from_dt DESC) AS to_dt
FROM orig_table

assumes that the from_dt is of type integer