Query return records that have 2 rows that equal zero

select
"DATA_ID",
"DATA_ID" DATA_ID_DISPLAY,
"CUSTNO",
"STORENO",
"SALEDATE",
"INVOICENO",
"UPC",
"SHIPTO",
SHIPNAME,
"PRICE",
"QTY",
"AMT",
"BRANCHNO",
"ITEMNO"
from (SELECT a.*, count(*) over(partition by Custno,Branchno,Shipto,INVOICENO, SALEDATE, UPC,
                    SHIPTO, PRICE, ABS(AMT)) cnt,
sum(AMT) over(partition by Custno,Branchno,Shipto,INVOICENO, SALEDATE, UPC,
                    SHIPTO, PRICE, ABS(AMT)) sumamt
       from "#OWNER#"."MY_DATA" a)
where cnt >1 AND NOT (cnt = 2 and sumamt = 0)  
order by Custno,Branchno,Shipto,Invoiceno,Itemno,PRICE,abs(AMT)

-- but this wil only exclude  a couple of rows in your current query.
-- and somehow i have the idea your are searching for something else
What  rows are you trying to find ?

Actually I think AND (cnt = 2 and sumamt = 0)   would do it... I'm looking at a business rule to find these records that cancel each other out and to not show them on the invoice.

Just got done with a conversation.. the rule might even be more tolerant.. to remove also those plus/minus .05 cents of the amount.
Weird I know..

But if that were the case.. how would I do that?

Thanks, Bill

Can you post some sample data and expected result?

And tell us the rule,  and don't forget to tell  on what level the rule should be evaluated. ( I doubt if price and abs(amt) should be on your partition clause).
Now
where cnt >1 AND NOT (cnt = 2 and sumamt = 0)  
if there are 3 rows with 1 pair all 3 rows will be shown
where cnt >1 AND NOT (cnt = 2 and sumamt > -0.05 and sumamt <= 0.05)     would  remove the plus/minus .05
but  such a difference cant exist if price is equal and abs(amount) is equal ?

535671      535671      161198      083455      02/18/2014      008210494662Yes, you are right, that will not work. The match that has more than 2 rows matching still needs to be available. Look at the 102.6 - 3 rows.. so it adds them. I need the query to exclude one of the positive rows and show the inverse match -102.6 and 102.6
Not sure if possible with SQL.. may have to use PL/SQL after I get the abs rows as below.

234	6656566	FORD #83455	3.95	.08	.33	008	171	0
535674	535674	161198	083455	02/18/2014	008210494662	234	6656566	FORD #83455	3.95	-.08	-.33	008	171	0
538766	538766	161198	083455	02/18/2014	008210494662	657	6656566	FORD #83455	3.4	-.17	-.57	008	182	0
538767	538767	161198	083455	02/18/2014	008210494662	657	6656566	FORD #83455	3.4	.16	.57	008	182	0
538768	538768	161198	013428	02/24/2014	014210554568	211	2112233	FORD #42877	5.13	20	102.6	014	800	102.6
537284	537284	161198	013428	02/24/2014	014210554568	211	2112233	FORD #42877	5.13	-20	-102.6	014	800	102.6
537276	537276	161198	013428	02/24/2014	014210554568	211	2112233	FORD #42877	5.13	20	102.6	014	800	102.6
534146	534146	161198	009026	02/17/2014	017010484328	981	1263329	FORD #02621	1.5	-10	-15	017	925	0
534144	534144	161198	009026	02/17/2014	017010484328	981	1263329	FORD #02621	1.5	10	15	017	925	0

Select allOpen in new window

The rule is that Custno, Branchno, Shipto, INVOICENO, SALEDATE, UPC, ITEMNO and price all need to be the same and the AMT needs to match but one being positive and the other being negative. I need to show only these pairs.. not a 3rd one..
Thank you, Bill

Sorry for the delay.. I haven't had time to test this out.
Will this week.
Thank you, Bill

Wow flow01!  You are a Guru for sure..

That works great!  I finally got back to this...

All I had to do is add () to the ROW_NUMBER function -- ROW_NUMBER()
and add the ROW_NUMBER() required "Order By" and it worked AWESOME!
Exactly as it should...

Perfect!