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,SLPRNSID,COMMANT

Open Billing table(OPENTBLE):
DOCNUMBR,DOCDATE,DOCAMNT,VOIDSTTS

Historical Billing Table(HISTTBLE):
DOCNUMBR,DOCDATE,DOCAMNT,VOIDSTTS

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?
LVL 1
rwheeler23Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_agx_Commented:
Do you really need a UNION? Couldn't you take all records from the sales table that do NOT have a matching record in either history table where VOIDSTTS = 1?

It could be refined, but ... assuming the shared key is DOCNUMBR, something like:
SELECT SUM(st.DOCAMNT) 
FROM   SALETBLE st
WHERE NOT EXISTS
            (
                   SELECT 1 FROM HISTTBLE h WHERE h.DOCNUMBR = st.DOCNUMBR AND ot.VOIDSTTS = 1

             )
AND  NOT EXISTS
            (
                   SELECT 1 FROM OPENTBLE ot WHERE ot.DOCNUMBR = st.DOCNUMBR AND ot.VOIDSTTS = 1

             )

Open in new window

rwheeler23Author Commented:
Very good point. Thank you.
PortletPaulEE Topic AdvisorCommented:
OR NOT EXISTS I think. If the document has been voided in either of those tables, and that means you need to exclude it from the sum, then use OR

SELECT SUM(st.DOCAMNT) 
FROM   SALETBLE st
WHERE NOT EXISTS
            (
                   SELECT 1 FROM HISTTBLE h WHERE h.DOCNUMBR = st.DOCNUMBR AND ot.VOIDSTTS = 1

             )
OR  NOT EXISTS
            (
                   SELECT 1 FROM OPENTBLE ot WHERE ot.DOCNUMBR = st.DOCNUMBR AND ot.VOIDSTTS = 1

             )

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

DougCommented:
Maybe you were thinking along these lines...

SELECT SUM(s.DocAmt) 
FROM SaleTable s
WHERE s.DocNumber 
	IN(SELECT o.DocNumbr FROM OpenTable o
	WHERE o.VOIDSTTS=0 
	UNION Select h.DocNumber 
	FROM HistTble h
	WHERE  h.VOIDSTTS=0)

Open in new window

PortletPaulEE Topic AdvisorCommented:
but using UNION in that context isn't efficient

UNION ALL would be "less expensive"
SELECT SUM(s.DocAmt) 
FROM SaleTable s
WHERE s.DocNumber 
	IN(SELECT o.DocNumbr FROM OpenTable o
	WHERE o.VOIDSTTS=0 

	UNION ALL

        Select h.DocNumber FROM HistTble h
	WHERE  h.VOIDSTTS=0)
;

Open in new window

But there should be little or no advantage in that over:
SELECT SUM(s.DocAmt) 
FROM SaleTable s
WHERE s.DocNumber 
	IN (SELECT o.DocNumbr FROM OpenTable o
	WHERE o.VOIDSTTS=0 
OR 	 s.DocNumber 
	IN (Select h.DocNumber FROM HistTble h
	WHERE  h.VOIDSTTS=0)
;

Open in new window

but EXISTS/NOT EXISTS is usually equally as efficient and can be more efficient then using IN ()
I would prefer to use EXISTS/NOT EXISTS if the data volumes are large.

{+edit}
but using execution plans is needed to do better tuning
_agx_Commented:
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 ;-)
rwheeler23Author Commented:
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.
_agx_Commented:
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:
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); 
 

Open in new window


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

             )
;         

Open in new window


Results:
Using AND ->  result  $ 50.00
Using OR ->   result   $ 150.00
PortletPaulEE Topic AdvisorCommented:
very sorry... humble pie etc

ignore me please
rwheeler23Author Commented:
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.
_agx_Commented:
However you want to allocate is fine.  Helping to find an answer is the important part :)

Edit: Paul Maxwell - Heh, no worries.
rwheeler23Author Commented:
Thanks again. By the way, the choice was 'AND' in this case. In my case it eliminated the one document that was voided.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.