Jayizzle Jayizzle - 6 months ago 8
SQL Question

How to return a table that removed SQL rows that match two columns

I have a table that returns some rows, what we are looking at is only if rxID matches and also if those amountPaid is opposite of eachother.

In the screenshot, we have two entries of an rxID of 3350593 that has an amount paid of 2.35 and -2.35. That is in a money type format.

My query so far, only returns those two rowsenter image description here because it is matching on rxId. y.amountpaid at the bottom, cannot be found, and t.amountpaid cannot be just selected while also grouping by t.rxid.

If I can get a query to remove those two rows that have the same rxID and inversed amount paids, I'd appreciative.

SELECT x.*
FROM
(
SELECT IsInstyRxFlag, pri.payerReceiptItemId, CONVERT(varchar,rx.oeDate, 101) rxOeDate, CONVERT(DECIMAL(6,2), drx.payerOblig) payerObligation, CONVERT(DECIMAL(6,2), pri.amountPaid) amountPaid, pri.rxId, CONVERT(DECIMAL(6,2), (pri.amountPaid - drx.payerOblig)) difference, rxeventid
FROM PayerReceiptItem pri
JOIN Rx ON rx.rxId = pri.rxId
JOIN DispenseRx drx ON rx.rxId = drx.rxId
WHERE CONVERT(DECIMAL(6,2), drx.payerOblig) <> CONVERT(DECIMAL(6,2), pri.amountPaid) AND payerReceiptID = 19781
) x
JOIN (SELECT t.rxID
FROM
(
SELECT IsInstyRxFlag, pri.payerReceiptItemId, CONVERT(varchar,rx.oeDate, 101) rxOeDate, CONVERT(DECIMAL(6,2), drx.payerOblig) payerObligation, CONVERT(DECIMAL(6,2), pri.amountPaid) amountPaid, pri.rxId, CONVERT(DECIMAL(6,2), (pri.amountPaid - drx.payerOblig)) difference, rxeventid
FROM PayerReceiptItem pri
JOIN Rx ON rx.rxId = pri.rxId
JOIN DispenseRx drx ON rx.rxId = drx.rxId
WHERE CONVERT(DECIMAL(6,2), drx.payerOblig) <> CONVERT(DECIMAL(6,2), pri.amountPaid) AND payerReceiptID = 19781
) t

GROUP BY t.rxID
HAVING COUNT(t.rxID) > 1) y ON y.rxID = x.rxid

--can't find y.amountpaid, x can be seen
--and y.amountPaid = x.amountPaid


Now working with this, but x.amount paid is invalid because it is not an aggregate function.

with
x AS (SELECT IsInstyRxFlag
, pri.payerReceiptItemId
, CONVERT(varchar,rx.oeDate, 101) rxOeDate
, CONVERT(DECIMAL(6,2), drx.payerOblig) payerObligation
, CONVERT(DECIMAL(6,2), pri.amountPaid) amountPaid
, pri.rxId
, CONVERT(DECIMAL(6,2), (pri.amountPaid - drx.payerOblig)) difference
, rxeventid
FROM PayerReceiptItem pri
JOIN Rx ON rx.rxId = pri.rxId
JOIN DispenseRx drx ON rx.rxId = drx.rxId
WHERE CONVERT(DECIMAL(6,2), drx.payerOblig) <> CONVERT(DECIMAL(6,2), pri.amountPaid) AND payerReceiptID = 19781)
SELECT x.*
FROM x


where not exists(
SELECT *
FROM x AS y
WHERE x.amountpaid = -1* y.amountpaid
AND x.difference = x.amountpaid
AND x.rxid = y.rxid
);

Answer

What result you expect? I simplify your query, look at this:

with 
  x AS (SELECT IsInstyRxFlag
  , pri.payerReceiptItemId
  , CONVERT(varchar,rx.oeDate, 101) rxOeDate
  , CONVERT(DECIMAL(6,2), drx.payerOblig) payerObligation
  , CONVERT(DECIMAL(6,2), pri.amountPaid) amountPaid
  , pri.rxId
  , CONVERT(DECIMAL(6,2), (pri.amountPaid - drx.payerOblig)) difference
  , rxeventid  
  FROM PayerReceiptItem pri 
  JOIN Rx ON rx.rxId = pri.rxId 
  JOIN DispenseRx drx ON rx.rxId = drx.rxId  
  WHERE CONVERT(DECIMAL(6,2), drx.payerOblig) <>  CONVERT(DECIMAL(6,2), pri.amountPaid) AND payerReceiptID = 19781)
SELECT x.*
FROM x
JOIN (
  SELECT t.rxID -- how you want to find amountpaid if you don't select this
  --you can select max or min from amountpaid, to not group by this field
  , min(amountpaid) min_amountpaid, max(amountpaid) max_amountpaid
  FROM x t
  GROUP BY t.rxID
  HAVING COUNT(t.rxID) > 1) y 
ON  y.rxID = x.rxid

EDIT: but if you only need exclude this two rows, try this query:

with 
  x AS (SELECT IsInstyRxFlag
  , pri.payerReceiptItemId
  , CONVERT(varchar,rx.oeDate, 101) rxOeDate
  , CONVERT(DECIMAL(6,2), drx.payerOblig) payerObligation
  , CONVERT(DECIMAL(6,2), pri.amountPaid) amountPaid
  , pri.rxId
  , CONVERT(DECIMAL(6,2), (pri.amountPaid - drx.payerOblig)) difference
  , rxeventid  
  FROM PayerReceiptItem pri 
  JOIN Rx ON rx.rxId = pri.rxId 
  JOIN DispenseRx drx ON rx.rxId = drx.rxId  
  WHERE CONVERT(DECIMAL(6,2), drx.payerOblig) <>  CONVERT(DECIMAL(6,2), pri.amountPaid) AND payerReceiptID = 19781)
SELECT x.*
FROM x
WHERE NOT EXISTS (SELECT 1 FROM x t WHERE t.rxID = x.rxID and t.amountPaid = -1 * x.amountPaid )
Comments