Determine if qualification has expired
I need to workout if someone's qualification has expired. Once someone has completed a course it expires in 3 years from the date of completion. If their qual has expired but they have a nominated date then that will count as being valid
What I currently have
EmployeeID course Completed Date Nominated
1 IT 2021-06-18
2 IT 2020-10-10
3 IT 2017-01-01
4 IT 2017-01-01 2022-05-01
What I would like
EmployeeID course Completed Date Nominated Expiry Valid
1 IT 2021-06-18 2024-06-18 Yes
2 IT 2020-10-10 2023-10-10 Yes
3 IT 2017-01-01 2020-01-01 No
4 IT 2017-01-01 2022-05-01 Yes
I've managed to create the expiry column, need help with the 'Valid' case expression
cast(dateadd(year,3 ,[Completed_Date]) as date) as [Expiry]