案例声明覆盖其他案例

Question

在以下案例语句中，将分配给具有2019年9月开始的开始期限的人，而应分配值'3'。

此外，尽管被排除在查询中，但在搜索中包括了"不感兴趣"的l._ stage。

SELECT
U.*,
L.ID AS Lead_ID,
CASE
WHEN (L.Age_Change > 180) OR (L.Age_Change IS NULL AND L._Stage IS NOT NULL) AND
(L._Stage NOT LIKE '%Not Interested%' AND
 L._Stage NOT LIKE '%Duplicate%') AND
(L.Start_Term NOT LIKE '%September 2019%' AND  
 L.Start_Term NOT LIKE '%September 2020%' AND 
 L.Start_Term NOT LIKE '%September 2021%') THEN '1' 
WHEN (L._Stage IS NULL AND L.Created_Date < DATEADD(day,-45, GETDATE()) AND 
(L.Start_Term NOT LIKE '%September 2019%' AND 
 L.Start_Term NOT LIKE '%September 2020%' AND 
 L.Start_Term NOT LIKE '%September 2021%') THEN '2'
WHEN (L.Age_Change > 180) OR (L.Age_Change IS NULL AND L._Stage IS NOT NULL) AND
(L._Stage NOT LIKE '%Not Interested%' AND
 L._Stage NOT LIKE '%Duplicate%') AND
(L.Start_Term LIKE '%September 2019%' AND 
 L.Start_Term LIKE '%September 2020%' AND 
 L.Start_Term LIKE '%September 2021%') THEN '3'
 ELSE NULL END AS Case_Logic

编辑

我已经将字段l.age_change更新为接受int的字段，并且在上面的查询中反映了此更改。

样本数据：

Lead ID  L.Age_Change L._Stage    L.Start_Term     Expected Case Logic
0001     200          Interested  September 2018   '1'
0002     NULL         Interested  September 2018   '1'
0003     NULL         Interested  September 2019   '3'
0004     182          Interested  September 2019   '3'
0005     250          Interested  September 2020   '3'

在上面的情况下，'0003'，'0004'和'0005'均分配为" 1"的案例逻辑值，而不是" 3"。

关于该问题的任何指导将不胜感激

Answer 1

除了Start_Term之外，您的CASE语句中还有差异。在CASE 3中，您使用的是L.Age_Last__Stage_Change < DATEADD(day,-180, GETDATE())，在CASE 1中，您正在使用(L.Age_Change < DATEADD(day,-180, GETDATE()))

因此，将其更改为以下语句，如果返回正确的结果，那么您知道这是因为您将L.Age_Change和L.Age_Last__Stage_Change混合在一起，并且需要更改逻辑。

WHEN (L.Age_Change < DATEADD(day,-180, GETDATE())) OR (L.Age_Change IS NULL 
AND L._Stage IS NOT NULL) AND
(L._Stage NOT LIKE '%Not Interested%' AND
 L._Stage NOT LIKE '%Duplicate%') AND
(L.Start_Term NOT LIKE '%September 2019%' AND  
 L.Start_Term NOT LIKE '%September 2020%' AND 
 L.Start_Term NOT LIKE '%September 2021%') THEN '1' 
WHEN (L._Stage IS NULL AND L.Created_Date < DATEADD(day,-45, GETDATE()) AND 
(L.Start_Term NOT LIKE '%September 2019%' AND 
 L.Start_Term NOT LIKE '%September 2020%' AND 
 L.Start_Term NOT LIKE '%September 2021%') THEN '2'
WHEN (L.Age_Last__Stage_Change < DATEADD(day,-180, GETDATE())) OR 
(L.Age_Last__Stage_Change IS NULL AND L._Stage IS NOT NULL) AND
(L._Stage NOT LIKE '%Not Interested%' AND
 L._Stage NOT LIKE '%Duplicate%') AND
(L.Start_Term LIKE '%September 2019%' AND 
 L.Start_Term LIKE '%September 2020%' AND 
 L.Start_Term LIKE '%September 2021%') THEN '3'

Answer 2

尝试以下内容。如果您缩进所有内容，那么看到错误在哪里要容易得多：

CASE
    WHEN (
                L.Age_Change < DATEADD(day,-180, GETDATE()) 
            OR  (L.Age_Change IS NULL AND L._Stage IS NOT NULL)
         ) 
        AND (
                    L._Stage NOT LIKE '%Not Interested%' 
                AND L._Stage NOT LIKE '%Duplicate%' 
                AND L.Start_Term NOT LIKE '%September 2019%'
                AND L.Start_Term NOT LIKE '%September 2020%' 
                AND L.Start_Term NOT LIKE '%September 2021%'
            ) THEN '1' 
    WHEN        L._Stage IS NULL AND L.Created_Date < DATEADD(day,-45, GETDATE()) 
            AND L.Start_Term NOT LIKE '%September 2019%' 
            AND L.Start_Term NOT LIKE '%September 2020%' 
            AND L.Start_Term NOT LIKE '%September 2021%' THEN '2'
    WHEN (
                L.Age_Change < DATEADD(day,-180, GETDATE()) 
            OR (L.Age_Change IS NULL AND L._Stage IS NOT NULL)
         ) 
        AND (
                    L._Stage NOT LIKE '%Not Interested%' 
                AND L._Stage NOT LIKE '%Duplicate%') 
                AND (
                            L.Start_Term LIKE '%September 2019%' 
                        OR  L.Start_Term LIKE '%September 2020%' --Changed AND to OR, because an value cannot be LIKE '%September 2019%' AND LIKE '%September 2020%', it's one or the other (I assume)
                        OR  L.Start_Term LIKE '%September 2021%'
                    )
            ) THEN '3'
 ELSE NULL 
 END

Answer 3

您正在混合OR和AND条件，而无需使用它们之间的括号，这绝不是一个好主意。AND将优先考虑，并且您的查询在更可读时，将看起来像这样：

WHEN 
L.Age_Change < DATEADD(day,-180, GETDATE())    
OR 
(
  L.Age_Change IS NULL 
  AND L._Stage IS NOT NULL 
  AND L._Stage NOT LIKE '%Not Interested%' 
  AND L._Stage NOT LIKE '%Duplicate%' 
  AND L.Start_Term NOT LIKE '%September 2019%' 
  AND L.Start_Term NOT LIKE '%September 2020%' 
  AND L.Start_Term NOT LIKE '%September 2021%'
) 
THEN '1' 

，如果第一部分为真或其他一切都是正确的，则将对TRUE进行评估。

您可能希望OR条件在括号中，例如：

WHEN 
(
   L.Age_Change < DATEADD(day,-180, GETDATE()) 
   OR 
   L.Age_Change IS NULL
)
AND L._Stage IS NOT NULL 
AND L._Stage NOT LIKE '%Not Interested%' 
AND L._Stage NOT LIKE '%Duplicate%' 
AND L.Start_Term NOT LIKE '%September 2019%' 
AND L.Start_Term NOT LIKE '%September 2020%' 
AND L.Start_Term NOT LIKE '%September 2021%'
THEN '1' 

在第三种情况下相同

还要注意，在行开头的AND/OR和每个支架的适当凹痕都可以帮助您犯错误。

Answer 4

由于情况1和3具有相似之处，也许如果我们将它们合并为一个嵌套的情况，将是一种更好的方法。

更新

SELECT
L.Lead_ID,
L.Age_Change, 
L._Stage, 
L.Start_Term,
CASE
    WHEN 
        (L.Age_Change > 180 OR L.Age_Change IS NULL )
    AND L._Stage IS NOT NULL
    AND L._Stage NOT IN('Not Interested', 'Duplicate')
    THEN 
        CASE
            WHEN  L.Start_Term NOT IN('September 2019', 'September 2020', 'September 2021') THEN 1
            WHEN  L.Start_Term IN('September 2019', 'September 2020', 'September 2021') THEN 3
        END
    WHEN 
        L.Age_Change BETWEEN 45 AND 180
    AND L._Stage IS NULL
    AND L.Start_Term IN('September 2019', 'September 2020', 'September 2021')   
    THEN 2
    ELSE NULL 
END AS Case_Logic
FROM test L

结果：

| Lead_ID | Age_Change |         _Stage |     Start_Term | Case_Logic |
|---------|------------|----------------|----------------|------------|
|       1 |        200 |     Interested | September 2018 |          1 |
|       2 |     (null) |     Interested | September 2018 |          1 |
|       3 |     (null) |     Interested | September 2019 |          3 |
|       4 |        182 |     Interested | September 2019 |          3 |
|       5 |        250 |     Interested | September 2020 |          3 |

这将避免您使用或在同一列上保持查询短而不是重复条件(即使它具有差异(。

，如果我们知道模式并有适当的样品进行测试，我们甚至可以使其短。

更新：

由于您清除了_Stage和Start_Term都是VARCHAR，而且它们不是其他字符串的一部分。您可以使用上述解决方案，因为LIKE如果该短语是另一个字符串(例如句子(的一部分，或者您不知道确切的匹配项会更好。但是IN如果(仅当它是确切的匹配(会做同样的事情。

您仍然可以使用其中的任何一个LIKE或IN，它仅取决于您的数据，并且哪个可以更好地效果。

注意，由于您没有提供足够的样本来覆盖第三个情况(情况＃2(，因此我试图复制案例和我添加到提供的查询的条件，并且一切都可以在示例逻辑中效果很好。

演示：sqlfiddle