如何翻译嵌套的CASE表达式以理解逻辑？

Question

我有一个由其他人创建的存储过程。在WHERE条款，还有的是，我有一个很难理解一个CASE表达式：

DECLARE 
     @DateFrom datetime = '01-01-2016', 
     @DateTo datetime = '12-31-2016' 
     @EffDateFrom datetime = NULL, 
     @EffDateTo datetime = NULL, 
    /* SOME SELECT statement here */ 
WHERE  
      CASE WHEN @EffDateFrom IS NULL THEN 1 
          ELSE CASE WHEN dateDiff(d, '01-01-2016', dbo.tblQuotes.EffectiveDate) >= 0 
            AND dateDiff(d, EOMONTH (GETDATE()), dbo.tblQuotes.EffectiveDate) <= 0 Then 1 
            else 0 
            end 
          END = 1 

--------------/* This is where I am confused */-------------------------------- 

         AND CASE WHEN @DateFrom IS NULL THEN 1 ELSE 
          CASE WHEN INV.InvoiceDate > INV.EffectiveDate THEN 
           CASE WHEN dateDiff(d, '01-01-2016', INV.InvoiceDate) >= 0 
            AND dateDiff(d, '12-31-2016', INV.InvoiceDate) <= 0 Then 1 else 0 end 
          ELSE 
           CASE WHEN dateDiff(d, '01-01-2016', INV.EffectiveDate) >= 0 
            AND dateDiff(d, '12-31-2016', INV.EffectiveDate) <= 0 Then 1 else 0 end 
          END 
          END = 1 

所以我们在这里说的是：

当参数@DateFrom为null，则使用参数@EffDateFrom ， 是对的吗？

但是，如果其不为空，然后 1，检查InvoiceDate大于EFFECTIVEDATE，如果它是 - 然后 2.检查是否01-01-2016和InvoiceDate之间的天数大于或等于0 AND如果该号码12-31-2016与InvoiceDate之间的天数小于或等于0，则为1！什么是1？这意味着记录是有效的？记录将在一张桌子上？正确？

而且ELSE 0这意味着它不会接的记录，是否正确？

之后，我很困惑。 更新的理解（如果正确）：

CASE WHEN @DateFrom IS NULL THEN 1 ELSE 

          CASE WHEN INV.InvoiceDate > INV.EffectiveDate THEN 
-----------------------------/*then check the below conditions and if its 1 then display the record if its 0 then do NOT */ 
             CASE WHEN dateDiff(d, '01-01-2016', INV.InvoiceDate) >= 0 
                AND dateDiff(d, '12-31-2016', INV.InvoiceDate) <= 0 THEN 1 ELSE 0 
             END 
----------------------------/* and this statement will only be working if parameter @EffDateFrom is not null. Correct? */ 
          ELSE 
           CASE WHEN dateDiff(d, '01-01-2016', INV.EffectiveDate) >= 0 
            AND dateDiff(d, '12-31-2016', INV.EffectiveDate) <= 0 Then 1 else 0 end 
          END 
          END = 1 

Answer 1

这是（部分的你问）的逻辑：

IF (@DateFrom IS NULL) 
    OR ((INV.InvoiceDate > INV.EffectiveDate) AND (dateDiff(d, '01-01-2016', INV.InvoiceDate) >= 0) AND (dateDiff(d, '12-31-2016', INV.InvoiceDate) <= 0)) 
    OR ((INV.InvoiceDate <= INV.EffectiveDate) AND (dateDiff(d, '01-01-2016', INV.EffectiveDate) >= 0) AND (dateDiff(d, '12-31-2016', INV.EffectiveDate) <= 0)) 
    OR ((INV.InvoiceDate IS NULL) AND (dateDiff(d, '01-01-2016', INV.EffectiveDate) >= 0) AND (dateDiff(d, '12-31-2016', INV.EffectiveDate) <= 0)) 
    OR ((INV.EffectiveDate IS NULL) AND (dateDiff(d, '01-01-2016', INV.EffectiveDate) >= 0) AND (dateDiff(d, '12-31-2016', INV.EffectiveDate) <= 0)) 
THEN TRUE 
ELSE FALSE 

整个声明：

IF (@EffDateFrom IS NULL) 
    OR ((dateDiff(d, '01-01-2016', dbo.tblQuotes.EffectiveDate) >= 0) AND (dateDiff(d, EOMONTH (GETDATE()), dbo.tblQuotes.EffectiveDate) <= 0)) 
THEN TRUE 
ELSE FALSE 

AND 

IF (@DateFrom IS NULL) 
    OR ((INV.InvoiceDate > INV.EffectiveDate) AND (dateDiff(d, '01-01-2016', INV.InvoiceDate) >= 0) AND (dateDiff(d, '12-31-2016', INV.InvoiceDate) <= 0)) 
    OR ((INV.InvoiceDate <= INV.EffectiveDate) AND (dateDiff(d, '01-01-2016', INV.EffectiveDate) >= 0) AND (dateDiff(d, '12-31-2016', INV.EffectiveDate) <= 0)) 
    OR ((INV.InvoiceDate IS NULL) AND (dateDiff(d, '01-01-2016', INV.EffectiveDate) >= 0) AND (dateDiff(d, '12-31-2016', INV.EffectiveDate) <= 0)) 
    OR ((INV.EffectiveDate IS NULL) AND (dateDiff(d, '01-01-2016', INV.EffectiveDate) >= 0) AND (dateDiff(d, '12-31-2016', INV.EffectiveDate) <= 0)) 
THEN TRUE 
ELSE FALSE 

Answer 2

零件时，感到困惑可以被解释为以下无需使用case语句：

WHERE 
      @DateFrom IS NULL 

OR ( @DateFrom IS NOT NULL 
     AND INV.InvoiceDate > INV.EffectiveDate 
     AND dateDiff(d, '01-01-2016', INV.InvoiceDate) >= 0 
     AND dateDiff(d, '12-31-2016', INV.InvoiceDate) <= 0 
    ) 

OR ( @DateFrom IS NOT NULL 
     AND dateDiff(d, '01-01-2016', INV.EffectiveDate) >= 0 
     AND dateDiff(d, '12-31-2016', INV.EffectiveDate) <= 0 
    )