How to build a query that pulls in a listing of employee ids for employees that have a value that occurred on a range of dates unique to employee?

I need to pull in the distinct employee IDs for any employee who had a corrective action with a list of corrective action type IDs within 180 days of their corrective action date.

so if I have employee John Doe who got a corrective action type id 123 on 07/08/2018 I need to know if he got a corrective action for type IDs 123, 456, or 789 within 180 days before their current corrective action or 60 days after.

In the above sample table I would want it to bring in Employee ID 1 and 5 as they have had a corrective action within 180 days.

Answer

You can do this with a join:

select c.*, cnext.*
from corrective c join
     corrective cnext
     on cnext.employee_id = c.employee_id and
        cnext.ca_dt > c.ca_dt - 180 and
        cnext.ca_dt < c.ca_dt + 60 and
        cnext.ca_type in (123, 456, 789)