Link to home
Start Free TrialLog in
Avatar of JDCam
JDCam

asked on

SQL 2005 - Exclude records from Select

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?
ASKER CERTIFIED SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

Avatar of JDCam
JDCam

ASKER

Excellent!!  Thanks
If you like to take a look at different types of subqueries other than Common Table Expression (CTE) used here, please see this link.
User generated image
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