I just discovered an issue with a script I have that sums values. This script sums order totals. This comes about because I have one table that holds sales document numbers and then these records get moved into a completely table for billing. The problem is caused because someone may void the sales document but only on the billing side. Compounding this is the fact that on the billing side the billing document can be in one of two tables. The table structures are:
Open Billing table(OPENTBLE):
Historical Billing Table(HISTTBLE):
So my current script is SELECT SUM(DOCAMNT) FROM SALETBLE
What I need to add is a UNION of OPENTBLE and HISTTBLE and look at the VOIDSTTS field. If it is 0, include the sum and if it is 1 exclude from the sum. In the sales table are additional fields for salesperson and commissions.
How do I structure the script so I can filter out the documents that have been voided?