JDCam
asked on
SQL 2005 - Exclude records from Select
Experts,
I use the following query today
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?
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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