SQL - ISNULL或如果不是最新日期

Question

我遇到了有两个日期字段的情况。

如果一个为Null，则取另一个。目前，使用下面的代码

可以正常工作

select e.EMPLOY_REF, ISNULL(e.PROB_DOCS_SENT, ec.USR_FINALPROB)
from EMPLOYEE_TABLE e
join EMPLOYEE_USERCUST ec on ec.EMPLOY_REF = e.EMPLOY_REF

然而，也有可能两者都填写了，如果填写了，我需要将最晚的日期作为有效日期。

在两个日期都已填写的情况下，我如何确保使用最晚的日期?

Answer 1

尝试下面的查询，此解决方案相对于case解决方案的优点是您可以轻松地将其扩展为更多列

SELECT EMPLOY_REF, MAX(Date)
FROM (
  select e.EMPLOY_REF, e.PROB_DOCS_SENT AS Date
  from EMPLOYEE_TABLE e
  join EMPLOYEE_USERCUST ec on ec.EMPLOY_REF = e.EMPLOY_REF
  union
  select e.EMPLOY_REF, ec.USR_FINALPROB AS Date
  from EMPLOYEE_TABLE e
  join EMPLOYEE_USERCUST ec on ec.EMPLOY_REF = e.EMPLOY_REF
) tbl
GROUP BY EMPLOY_REF

Answer 2

尝试以下操作-这适用于MSSQL

select e.EMPLOY_REF,  
case when   e.PROB_DOCS_SENT is null and ec.USR_FINALPROB is not null  then ec.USR_FINALPROB
    else   e.PROB_DOCS_SENT is not null and ec.USR_FINALPROB is null then e.PROB_DOCS_SENT
    else   e.PROB_DOCS_SENT is not null and ec.USR_FINALPROB is not null and e.PROB_DOCS_SENT >ec.USR_FINALPROB then e.PROB_DOCS_SENT
    else ec.USR_FINALPROB end
from EMPLOYEE_TABLE e
join EMPLOYEE_USERCUST ec on ec.EMPLOY_REF = e.EMPLOY_REF

Answer 3

试试这个:

SELECT 
   ID,
   CASE 
        WHEN Date1 > Date2 
        THEN Date1
        ELSE Date2
   END AS MostRecentDate
FROM Table

Answer 4

您可以使用CASE-WHEN来处理所有可能的情况。在两个日期都为空的情况下，下面的查询将以今天的日期作为值。

select e.EMPLOY_REF, ISNULL(e.PROB_DOCS_SENT, ec.USR_FINALPROB)
case when e.PROB_DOCS_SENT is null and ec.USR_FINALPROB is null then getdate()
     when e.PROB_DOCS_SENT is null and ec.USR_FINALPROB is not null then ec.USR_FINALPROB
     when e.PROB_DOCS_SENT is not null and ec.USR_FINALPROB is null then e.PROB_DOCS_SENT
     when e.PROB_DOCS_SENT is not null and ec.USR_FINALPROB is not null 
     then 
         case when e.PROB_DOCS_SENT> ec.USR_FINALPROB then e.PROB_DOCS_SENT
              when e.PROB_DOCS_SENT> ec.USR_FINALPROB then ec.USR_FINALPROB
         end
end
from EMPLOYEE_TABLE e
join EMPLOYEE_USERCUST ec on ec.EMPLOY_REF = e.EMPLOY_REF