Reporting Stored Procedure - How to avoid duplication?

Question

I'm writing a reporting stored procedure. I want to get the number of non-Acknowledged and non-Invoiced Purchase Orders, with the ability to (optionally) filter on CustomerID.

What I have below works as expected, but I worry that a) it's slow and b) there's duplication in the CustomerID portion of the WHERE clause.

How would you write this stored proc, Stack Overflow?

SELECT 
  (SELECT COUNT(*)
   FROM PurchaseOrder
   WHERE AcknowledgmentStatus <> 'Complete'
   AND (@CustID = 0 OR CustomerID = @CustID)
  ) AS NonAckd,
  (SELECT COUNT(*)
   FROM PurchaseOrder
   WHERE InvoiceStatus <> 'Complete'
   AND (@CustID = 0 OR CustomerID = @CustID)
  ) AS NonInvoiced

Answer 1

I'm not sure 100% what you are after, but you could simplify the customer part as follows: (untested from [tired] memory)

set @custID = nullif(@custID,0)

SELECT 
  (SELECT COUNT(*)
   FROM PurchaseOrder
   WHERE AcknowledgmentStatus <> 'Complete'
   AND ( CustomerID = isnull(@CustID,CustomerID) )
  ) AS NonAckd,
  (SELECT COUNT(*)
   FROM PurchaseOrder
   WHERE InvoiceStatus <> 'Complete'
   AND ( CustomerID = isnull(@CustID,CustomerID) )
  ) AS NonInvoiced

Answer 2

Something like this:

SELECT Sum(Case When AcknowledgmentStatus <> 'Complete' Then 1 ELSE 0 END) as NonAckd
      ,Sum(Case When InvoiceStatus <> 'Complete'        Then 1 ELSE 0 END) as NonInvoiced 
  FROM PurchaseOrder 
 WHERE CustomerID = IsNull(NullIf(@CustID, 0), CustomerID) 

Answer 3

You can't use:

• COUNT because it will count all rows (1 or 0 is not null so it counts them)
• COUNT DISTINCT will give 2 (only values 1 and 0)

If you remove the status checks it will run faster of course if you can deal with zeros.

SELECT
    CASE WHEN AcknowledgmentStatus <> 'Complete' THEN 1 ELSE 0 END AS NonAckd,
    CASE WHEN InvoiceStatus <> 'Complete' THEN 1 ELSE 0 END AS NonInvoiced,
FROM
    PurchaseOrder
WHERE
    (AcknowledgmentStatus <> 'Complete' OR  InvoiceStatus <> 'Complete') --optional
    AND
    (@CustID = 0 OR CustomerID = @CustID)