case statement to find if qualification has expired-CodePudding

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 statement

cast(dateadd(year,3 ,[Completed_Date]) as date) as [Expiry]

CodePudding user response：

Using cross apply for DRY and case expressions:

select t.*, expiry, case
    when nominated >= currdate then 'yes'
    when expiry >= currdate then 'yes'
    else 'no'
end as valid
from t
cross apply (select
    cast(current_timestamp as date),
    dateadd(year, 3, completed_date)
) as ca(currdate, expiry)

CodePudding user response：

You need Subquery and iifto get your desired result,first use following query to produce

your data

declare @a TABLE(
  EmployeeID Int NOT NULL, 
  course VARCHAR(30) NOT NULL, 
  completed_date Date, 
  Nominated Date
);
INSERT INTO @A(
  EmployeeID, course, completed_date, Nominated
) 
VALUES 
  (1, 'IT', '2021-06-18', NULL), 
  (2, 'IT', '2020-10-10', NULL), 
  (3, 'IT', '2017-01-01', NULL), 
  (4, 'IT', '2017-01-01', '2022-05-01');

Expiray column as follows

 Iif(nominated IS NULL, Cast(Dateadd(year, 3, completed_date) AS DATE),
               NULL)
                      AS
               [Expiry]

then use subquery as follows

SELECT employeeid,
       course,
       completed_date,
       nominated,
       expiry,
       Iif(expiry < Getdate()
            OR nominated < Getdate(), 'No', 'Yes') valid
FROM   (SELECT employeeid,
               course,
               completed_date,
               nominated,
               Iif(nominated IS NULL, Cast(Dateadd(year, 3, completed_date) AS DATE),
               NULL)
                      AS
               [Expiry]
        FROM   @a) B