Table for a specific ORDERID
ORDERID  LINE  QTY   OPID
---------------------------
PDP34533  1     3    4465
PDP34533  2     1    2356
PDP34533  3     2    3512
PDP34533  4    -2    52657
PDP34533  5     2    52657

PDP34533  1     3    4465
PDP34533  2     1    2356
PDP34533  3     2    3512

SELECT
	OrderId,
	Line,
	Qty,
	Optid
FROM
	YourTable y1
WHERE
	y1.Opid NOT IN
	( SELECT y2.opid FROM YourTable y2
	  GROUP BY y2.opid
	  HAVING MIN(y2.Qty) < 0
	);

-- *** Test Data ***
CREATE TABLE #t
(
	OrderID varchar(20) NOT NULL
	,Line tinyint NOT NULL
	,Qty smallint NOT NULL
	,OPID int NOT NULL
);
INSERT INTO #t
SELECT 'PDP34533', 1, 3, 4465
UNION ALL SELECT 'PDP34533', 2, 1, 2356
UNION ALL SELECT 'PDP34533', 3, 2, 3512
UNION ALL SELECT 'PDP34533', 4, -2, 52657
UNION ALL SELECT 'PDP34533', 5, 2, 52657;
-- *** End Test Data ***

WITH SQtys
AS
(
	SELECT OrderId, Line, Qty, OPID
		,SUM(Qty) OVER (PARTITION BY OrderID, OPID) AS SQty
	FROM #t
)
SELECT OrderId, Line, Qty, OPID
FROM SQtys
WHERE SQty <> 0
ORDER BY OrderID, Line;

SELECT t.*
FROM table t
OUTER APPLY(SELECT COUNT(*) AS Cnt
            FROM table
            WHERE ORDERID = t.ORDERID
            AND OPID = t.OPID
            AND QTY = -1 * t.QTY
           )t1
WHERE t1.Cnt=0

SELECT *
FROM table AS t
WHERE
    t1.[QTY] > 0
    AND NOT EXISTS (
    SELECT 1
    FROM table AS t2
    WHERE
        t2.[orderID] = t1.[OrderID]
        AND t2.[QPID] = t1.[QPID]
        AND t2.[QTY] < 0
        AND t2.[QTY] + t1.[QTY] = 0
    )

quote:

---

Originally posted by Transact Charlie

another....

SELECT *
FROM table AS t
WHERE NOT EXISTS (
    SELECT 1
    FROM table AS t2
    WHERE
        t2.[orderID] = t1.[OrderID]
        AND t2.[QPID] = t1.[QPID]
        AND t2.[QTY] < 0
    )

Don't know about speed but it's a bit more declarative. Can use indexes on orderID, QPID and QTY. Especially a filtered index on QTY might work well.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

---

quote:

---

Originally posted by sunitabeck

Both of you must be seeing something I am not. Although one can infer that OP is looking for matching quantities with opposing signs, in the original posting, the request was "The ommited lines should be double checked by matching both ORDERID AND OPID". Specifically not mentioned was the requirement that QTY should be negative of one another.

Oh well! OP probably solved his/her problem and is thinking about enjoying the weekend, and here we are analyzing it to death.

---

SELECT t.*
FROM table t
OUTER APPLY(SELECT COUNT(*) AS Cnt
            FROM table
            WHERE ORDERID = t.ORDERID
            AND OPID = t.OPID
            AND QTY < 0
           )t1
WHERE t1.Cnt=0

SELECT *
FROM table AS t
WHERE
    NOT EXISTS 
    (
		SELECT 1
		FROM table AS t2
		WHERE
			t2.[orderID] = t1.[OrderID]
			AND t2.[QPID] = t1.[QPID]
			AND t2.[QTY] < 0
    )

quote:

---

Originally posted by barnabeck

Visakh, I don't know if this is correct as it makes me think that you filter only the QTY < 0. This probably doesn't make sense to you either, but it's the way these guys work.
Again (...and I really adore your patience):
1. look for any negative QTY and get the OPID for that record
2. exclude all records with that OPID and that ORDERID regardless their QTY (it only needs one negative QTY to exclude all records with that OPID)
3. do this for all ORDERID
I hope I made it clear... and I said it was tricky ;)
Martin

---