SQL 2005 - Exclude records from Select

JDCam
JDCam used Ask the Experts™
on
Experts,
I use the following query today
select
	REF,
	DATE,
	LEV1,
	LEV2,
	COALESCE(MAX(FROM_CODE),'UNR')as FROM_CODE,
	COALESCE(SUM(FROM_QTY), (SUM(TO_QTY)*-1))as FROM_QTY,
	COALESCE(MAX(TO_CODE),'UNR')as TO_CODE,
	COALESCE(SUM(TO_QTY), (SUM(FROM_QTY)*-1)) as TO_QTY	
	FROM BVF_947
	GROUP BY REF, DATE, LEV1, LEV2

Open in new window


Before the Group BY, each row will contain a FROM_CODE or a TO_CODE, but rarely both.
After the Group By each summarized record will always have both a FROM_CODE and TO_CODE

What I want to do, is exlcude any rows where the value is the same in both columns.  If we need to specify them there are only 4 values (UNR, QI, BLK, DIS or XX).  Can anyone help me do this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database and Application Developer
Commented:
;with D
As
(
select
	REF,
	DATE,
	LEV1,
	LEV2,
	COALESCE(MAX(FROM_CODE),'UNR')as FROM_CODE,
	COALESCE(SUM(FROM_QTY), (SUM(TO_QTY)*-1))as FROM_QTY,
	COALESCE(MAX(TO_CODE),'UNR')as TO_CODE,
	COALESCE(SUM(TO_QTY), (SUM(FROM_QTY)*-1)) as TO_QTY	
	FROM BVF_947
	GROUP BY REF, DATE, LEV1, LEV2
)
Select * From D
Where FROM_CODE <> TO_CODE;

Open in new window

Mike EghtebasDatabase and Application Developer

Commented:
To include counts with a separate query:

;with D
As
(
select
	REF,
	DATE,
	LEV1,
	LEV2,
	COALESCE(MAX(FROM_CODE),'UNR')as FROM_CODE,
	COALESCE(SUM(FROM_QTY), (SUM(TO_QTY)*-1))as FROM_QTY,
	COALESCE(MAX(TO_CODE),'UNR')as TO_CODE,
	COALESCE(SUM(TO_QTY), (SUM(FROM_QTY)*-1)) as TO_QTY	
	FROM BVF_947
	GROUP BY REF, DATE, LEV1, LEV2
)
Select *, Count(*) From_To_Matches From D
Where FROM_CODE = TO_CODE

Open in new window

Author

Commented:
Excellent!!  Thanks
Mike EghtebasDatabase and Application Developer

Commented:
If you like to take a look at different types of subqueries other than Common Table Expression (CTE) used here, please see this link.
Subqueries
Mike EghtebasDatabase and Application Developer

Commented:
correction on the count solution:
;with D
As
(
select
	REF,
	DATE,
	LEV1,
	LEV2,
	COALESCE(MAX(FROM_CODE),'UNR')as FROM_CODE,
	COALESCE(SUM(FROM_QTY), (SUM(TO_QTY)*-1))as FROM_QTY,
	COALESCE(MAX(TO_CODE),'UNR')as TO_CODE,
	COALESCE(SUM(TO_QTY), (SUM(FROM_QTY)*-1)) as TO_QTY	
	FROM BVF_947
	GROUP BY REF, DATE, LEV1, LEV2
)
Select REF, Count(REF) From_To_Matches From D
Where FROM_CODE = TO_CODE
Group By REF;

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial