I have 2 tables, tbINVOICE and tbFEE. tbFee contains fees that get populated at the end of an invoice. As a result of a setup error, I have some invoices that are missing a fee that should have been applied. The tables join together on the invoice number so if I query invoice number 012457 in tbFee I get 3 records
INVNUM FEE CHARGE
012457 | FUEL | 3.58
012457 | CASE | 2.47
012457 | SRVCHG | 4.58
What is missing on 3 weeks worth of invoices is the SRVCHG. There is a field in the tbINVOICE call ORDTYPE and if ORDTYPE = 100 then it gets the SRVCHG fee.
I need a query that selects all INVNUM with an ORDTYPE = 100 that is missing the SRVCHG.