SQLTeam.com | Weblogs | Forums

Exclude on net $0?


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.


In your query, add a having clause.

   TotalInvoiceAmount <> 0


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.

   TotalInvoiceAmount <> 0
   OR TotalInvoiceCOUNT = 1

FROM dbo.invoice_table it
    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


Thank you!

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


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.


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