SQL Syntax with unions and joins
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:
Sales Table(SALETBLE):
DOCNUMBR,DOCDATE,DOCAMNT,S
Open Billing table(OPENTBLE):
DOCNUMBR,DOCDATE,DOCAMNT,V
Historical Billing Table(HISTTBLE):
DOCNUMBR,DOCDATE,DOCAMNT,V
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?
Sales Table(SALETBLE):
DOCNUMBR,DOCDATE,DOCAMNT,S
Open Billing table(OPENTBLE):
DOCNUMBR,DOCDATE,DOCAMNT,V
Historical Billing Table(HISTTBLE):
DOCNUMBR,DOCDATE,DOCAMNT,V
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Paul Maxwell wrote:
OR NOT EXISTS I think.
EDIT: Paul Maxwell - Well, my take was the VOIDSTTS=1 record could be in either of those tables and that there could be records for the same document number. In which case you would need to check both to ensure none of the records are marked VOIDSTTS=1. Hence the AND, rather than OR ...
(... OR my brain might just be really tired ;-)
ASKER
Join the club. My brain checked out several hours ago. In my case, never would the same doc number exist in both the open and historical tables. It will be either one or the other. That was what got me started on thinking a union would be needed.
Heh. I still think you need AND. If you use OR, it will include the document if at least one of the history tables doesn't contain a record with VOIDSTTS=1. Sounds like that will always be true. From what you described, I don't think that's the result you want.
Example:
SQL:
Results:
Using AND -> result $ 50.00
Using OR -> result $ 150.00
Example:
create table SALETBLE ( DOCNUMBR int, DOCAMNT decimal(10,2) );
create table OPENTBLE ( DOCNUMBR int, VOIDSTTS bit);
create table HISTTBLE ( DOCNUMBR int, VOIDSTTS bit );
insert into SALETBLE ( DOCNUMBR, DOCAMNT ) values (1234, 50.00), (4567, 100.00);
insert into OPENTBLE (DOCNUMBR, VOIDSTTS) values (1234, 0), (4567, 1);
SQL:
SELECT SUM(st.DOCAMNT)
FROM SALETBLE st
WHERE NOT EXISTS
(
SELECT 1 FROM HISTTBLE h WHERE h.DOCNUMBR = st.DOCNUMBR AND h.VOIDSTTS = 1
)
AND NOT EXISTS
(
SELECT 1 FROM OPENTBLE ot WHERE ot.DOCNUMBR = st.DOCNUMBR AND ot.VOIDSTTS = 1
)
;
SELECT SUM(st.DOCAMNT)
FROM SALETBLE st
WHERE NOT EXISTS
(
SELECT 1 FROM HISTTBLE h WHERE h.DOCNUMBR = st.DOCNUMBR AND h.VOIDSTTS = 1
)
OR NOT EXISTS
(
SELECT 1 FROM OPENTBLE ot WHERE ot.DOCNUMBR = st.DOCNUMBR AND ot.VOIDSTTS = 1
)
; Results:
Using AND -> result $ 50.00
Using OR -> result $ 150.00
very sorry... humble pie etc
ignore me please
ignore me please
ASKER
Hey, I appreciate all the help folks. The hard part now will be awarding points. You have all given me useful ideas and I am much further along now then a was a few hours ago.
However you want to allocate is fine. Helping to find an answer is the important part :)
Edit: Paul Maxwell - Heh, no worries.
Edit: Paul Maxwell - Heh, no worries.
ASKER
Thanks again. By the way, the choice was 'AND' in this case. In my case it eliminated the one document that was voided.
ASKER