Teradata SQL help. Need help getting the start date and end date (yellow) of the most recent employment status. Thank you

Teradata SQL help. Need help getting the start date and end date (yellow) of the most recent employment status. Thank you. Click on the question for image.

Answer

There are several ways to get your expected result, based on your data you might apply Teradata’s NORMALIZE option, a SQL extension to combine overlapping periods:

SELECT NAME, job_title, status, next(Begin(pd)) AS start_date, End(pd) AS end_date
FROM
 ( -- returns one group for consecutive overlapping rows 
   SELECT NORMALIZE
     name, 
     job_title,
     status,
      -- need to subtract 1 to create a valid period
     PERIOD(prior(start_date), end_date) AS pd
   FROM tab
 ) AS dt
QUALIFY
   -- return the latest row only
   Row_Number()
   Over(PARTITION BY name
        ORDER BY start_date DESC) = 1

Caution : This returns a new group whenever name, job_title or status change.