sql - 如何通过CASE语句中的值进行搜索？

我添加了一个CASE语句来获取将在报告中显示的发票状态值。现在，我希望能够按报表中的InvoiceStatus值（未清，已付，部分）进行搜索。我已经尝试了几件事，但是我什么都做不了。任何想法如何做到这一点？

ALTER PROCEDURE [dbo].[Extranet_ClientMatterInvoices]
@BeginDate VARCHAR(max) = NULL,
@EndDate VARCHAR(max) = NULL,
@InvoiceStatus VARCHAR(MAX) = NULL

AS
SELECT m.ClientGroupID,
m.ClientID,
m.ClientName,
m.MatterCode,
m.[Area Of Law],
i.InvoiceNo,
i.InvoiceTotalAmount,
i.InvoiceFees,
i.InvoiceCosts,
m.mattername,
i.invoiceDate,
 CASE
  WHEN i.InvoiceTotalAmount <= 0 THEN 'Paid'
  WHEN i.Total_PaidWithWO = 0 THEN 'Open'
  WHEN i.Total_PaidWithWO < i.InvoiceTotalAmount THEN 'Partial'
  WHEN i.Total_PaidWithWO >= i.InvoiceTotalAmount THEN 'Paid'
ELSE 'Open'
END AS 'InvoiceStatus'
 FROM [local].[lp2warehouse].dbo.lp2_matters m
 INNER JOIN [local].[lp2warehouse].dbo.lp2_invoices i
ON m.mattercode = i.mattercode
WHERE (i.invoicedate BETWEEN ISNULL(@BeginDate,'01/01/1900')
AND ISNULL(@EndDate,'12/31/9999'))
ORDER BY m.ClientGroupID,m.ClientID

最佳答案

将现有的SELECT包装到派生表或CTE中，然后可以在外部查询的InvoiceStatus过滤器中使用投影别名WHERE，即

SELECT * FROM
(
  SELECT
   m.ClientGroupID,
   m.ClientID,
   ... other fields
   CASE
     WHEN i.InvoiceTotalAmount <= 0 THEN 'Paid'
     WHEN i.Total_PaidWithWO = 0 THEN 'Open'
     WHEN i.Total_PaidWithWO < i.InvoiceTotalAmount THEN 'Partial'
     WHEN i.Total_PaidWithWO >= i.InvoiceTotalAmount THEN 'Paid'
     ELSE 'Open'
   END AS InvoiceStatus
 FROM ... same
 WHERE ... same
) derived
WHERE derived.InvoiceStatus = 'Partial';

关于sql - 如何通过CASE语句中的值进行搜索？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/28722874/