How can I look up values ​​from a CASE statement?

Question

I added a CASE statement to get the account status values ​​that will be displayed in the report. Now I want to be able to search for InvoiceStatus values ​​(open, paid, partial) in the report. I tried a few things, but I can not get it to work. Any idea how to do this?

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

Answer 1

Wrap an existing one SELECTin a view or CTE, and then you can use the projected alias InvoiceStatusin WHEREan external query filter, namely

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';