asked on 7/9/2015

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?

Microsoft SQL Server 2005 SQL

Last Comment

Mike Eghtebas

7/9/2015

ASKER CERTIFIED SOLUTION

Mike Eghtebas

7/9/2015

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Mike Eghtebas

7/9/2015

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

JDCam

7/9/2015

ASKER

Excellent!! Thanks

Mike Eghtebas

7/9/2015

If you like to take a look at different types of subqueries other than Common Table Expression (CTE) used here, please see this link.

Mike Eghtebas

7/9/2015

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