Exclude on net $0?

Transact-SQL
1

I am trying to figure out the correct syntax to exclude all invoices when the net amount is $0 due to returns.

I was able to extract the original invoice on the return invoices so that I have the original invoice record for, say $12 then a return invoice now populated in the invoice number field of the original invoice the return is for with a negative $12. So when these are grouped, since it's on the same invoice number now, the net amount of $12 - $12 = $0

Or whichever way to excludes these invoices since they cancel each other out.
They do not want to see these invoices that cancel each other out.

2

In your query, add a having clause.

HAVING
   TotalInvoiceAmount <> 0
3

How about for those invoices that are actually $0?
Can there be something like IF there's more than one of the same invoice and that equals $0, then exclude it? Otherwise it needs to show the other invoices.

4 
HAVING
   TotalInvoiceAmount <> 0
   OR TotalInvoiceCOUNT = 1
5 
SELECT it.*
FROM dbo.invoice_table it
INNER JOIN (
    SELECT invoice_number
    FROM dbo.invoice_table
    GROUP BY invoice_number
    HAVING SUM(invoice_amount) <> 0 AND COUNT(*) > 1
) AS it_nonzero ON it_nonzero.invoice_number = it.invoice_number
6

Thank you!

I am getter an error message:
Operand data type varchar is invalid for sum operator.

7

You'd need to cast it as decimal(19,3), or some other numeric format, before summing it then. Of course with varchar you always run the risk that there will be non-numeric data in the column that causes an error when you cast/convert it.

8

Hi Scott, not sure what and how??

Example data set:

2016-07-27 00:00:00.000LTS0006046608347ReturnDUES-LTSMBLTS-RMEM-12.000002016-07-20 00:00:00.000LTS00061308855557InvoiceDUES-LTSMBLTS-RINST12.000002016-08-03 00:00:00.000LTS00061308855557ReturnDUES-LTSMBLTS-RINST-12.000002016-07-31 00:00:00.000LTS00061866533599ReturnDUES-LTSMBLTS-RMEM-12.000002016-07-01 00:00:00.000LTS00062052101InvoiceDUES-LTSMBLTS-PRGM50.00000