sql - WHERE 子句中的 CASE

Question

下面是我当前的 SQL Server 2012 查询。基本上我想要最后一个工作日的信息，但在星期一，我希望它提取星期五的信息而不是星期日的信息。这是我到目前为止在查询中得到的内容，但它不会接受它。

USE [LetterGeneration]

SELECT 
    g.LetterGenerationPrintJobId,
    CONVERT(CHAR(12), r.CreatedDate, 101) AS CreatedDate,
    YEAR(r.CreatedDate) AS Year,
    MONTH(r.CreatedDate) AS Month,
    DAY(r.CreatedDate) AS Day,
    CASE 
       WHEN DATEPART(dw, r.CreatedDate) = 1 
          THEN 1
       WHEN DATEPART(dw, r.CreatedDate) = 7 
          THEN 1
       ElSE 0
    END AS Weekend,
    s.LetterGenerationStatusId AS Status,
    COUNT(g.LetterGenerationId) AS LetterCount,
    SUM(g.LetterPageCount) AS PageCount,
    t.IsLitigationCoverLetterAllowed,
    CASE 
       WHEN g.CarrierTrackingNumber LIKE '%1ZE%' 
          THEN 1
       WHEN g.CarrierTrackingNumber LIKE '921489%' 
          THEN 2
       WHEN g.CarrierTrackingNumber LIKE '917190%' 
          THEN 2
       ELSE 3
    END AS CarrierType
FROM 
    [LetterGenerationTemplateRequest] AS R
INNER JOIN 
    [LetterGenerationTemplate] AS T ON t.[LetterGenerationTemplateId] = r.LetterGenerationTemplateId
INNER JOIN 
    LetterGeneration G ON g.LetterGenerationTemplateRequestId = r.LetterGenerationTemplateRequestId
INNER JOIN 
    LetterGenerationStatus S ON g.LetterGenerationStatusId = s.LetterGenerationStatusId
WHERE 
    (CASE 
        WHEN (DATENAME(dw,GETDATE()) = 'Monday') 
           THEN (DATEDIFF(d, r.CreatedDate, GETDATE()) = 3)
        ELSE (DATEDIFF(d, r.CreatedDate, GETDATE()) = 1)
     END) 
    AND t.[TemplateKey] NOT LIKE '%PLTV1%'
    AND s.LetterGenerationStatusId = 19
ORDER BY 
    r.CreatedDate DESC, g.LetterGenerationPrintJobId DESC

为了使其按照我的想法工作，我对 WHERE 子句遗漏或误解了什么？

谢谢

Answer 1

也许可以转换为常规的 AND/OR？

WHERE (
    ((DATENAME(dw,GETDATE()) = 'Monday') AND (DATEDIFF(d, r.CreatedDate, GETDATE()) = 3))
    OR 
    (DATEDIFF(d, r.CreatedDate, GETDATE()) = 1)
) 
....