I am trying to figure out a syntax / code to:
EXCLUDE all invoices that does not have a registration line item but has an ADMINFEE and/or SHIPPING line item.
Or simply EXCLUDE all invoices that does not have a registration line item.
So the goal is to not display any invoice ONLY having SHIPPING and/or ADMINFEE.
Invoices may include, but not limited to, and in any combination of:
1. Registration and it's amount - (in the sample below it only shows DUES-LTSMB, this can be other Item codes / registration type)
2. ADMINFEE and it's amount - this is a static Item code
3. SHIPPING and it's amount - this is a static Item code
in this sample, InvNo LTS0001446, in red, would be excluded in the query result (using SQL View or SQL Query on SQL Server Management Studio)
InvNo Registration Admin/Ship CustNo ItemNo
LTS0001445 12.00000 0.00000 ORG00002838 DUES-LTSMB LTS0001445 0.00000 0.25000 ORG00002838 SHIPPING LTS0001445 0.00000 1.00000 ORG00002838 ADMINFEE LTS0001446
0.00000 0.25000 ORG00004402 SHIPPING LTS0001446 0.00000 1.00000 ORG00004402 ADMINFEE LTS0001451 12.00000 0.00000 PER01592434 DUES-LTSMB LTS0001451 0.00000 0.25000 PER01592434 SHIPPING LTS0001451 0.00000 1.00000 PER01592434 ADMINFEE
InvNo: LTS0001446 does not have a Registration line item, only SHIPPING and ADMINFEE (noting again that not all invoices have both)
I cannot figure out how to do this.