案例陈述，以确定资格是否已过期

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

我已经创建了到期列，需要"有效"事例语句的帮助

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

Answer 1

对DRY和case表达式使用cross apply：

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