case语句以查找资格是否已过期

Question

我需要锻炼，如果某人的资格已经过期。一旦有人完成了一个课程，它将在3年后结束之日结束。如果他们的数量已经过期，但是他们有一个指定的日期，那就算有效了。

我现在拥有的

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

我想要什么

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

我已经成功地创建了到期列，需要帮助处理‘有效’的case语句

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

Answer 1

将cross apply用于DRY和case表达式：

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)

Answer 2

您需要Subquery和iif来获得您想要的结果，首先使用下面的查询来生成

你的数据

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列如下

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

然后按以下方式使用子查询

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