Fetch conditional rows in SQL server-CodePudding

I need a query like below. ApplicationID and InvoiceNumber columns show purchases made. Negative values in the Revenue rows indicate shopping refund. The ApplicationID column does not change when the purchase is refunded, but the InvoiceNumber column changes for the refund. I determine the returns according to the price totals of different InvoiceNumbers in the same ApplicationID equal to zero. For example, customer A bought 4 products that InvoiceNumber=AA in ApplicationID=11 shopping, but refund 2 of them (InvoiceNumber=BB). I want to get the remaining rows after the refunds are extracted. So in this example, rows 1-2 and 5-6 will eliminate each other for ApplicationID=11 and only rows 3-4 will remain. In addition, ApplicationID=22 and ApplicationID=33 rows will also come as it does not contain refunds. Finally, rows 3,4,7, 8 and 9 will get. How do I do this?

CustomerCode    ApplicationID   InvoiceNumber   Date    Revenue
A                     11              AA      1.01.2020   150
A                     11              AA      2.01.2020   200
A                     11              AA      1.01.2020   250
A                     11              AA      1.01.2020   300
A                     11              BB      5.01.2020  -150
A                     11              BB      5.01.2020  -200
A                     22              CC      7.02.2020   500
A                     22              DD      7.02.2020   700
A                     11              AA      2.01.2020   800

I wrote the result I want. I want to subtract zero sum of revenue according to CustomerCode and ApplicationID and fetch all other columns

example code:

      select a.CustomerCode,a.ApplicationID from Table a
      group by CustomerCode,a.ApplicationID             
  having SUM(Revenue)>0

My desired result:

CustomerCode    ApplicationID   InvoiceNumber   Date    Revenue
A                     11              AA      1.01.2020   250
A                     11              AA      1.01.2020   300
A                     22              CC      7.02.2020   500
A                     22              DD      7.02.2020   700
A                     11              AA      2.01.2020   800

CodePudding user response：

I think you've gone down a route of needing to sum your results to remove certain rows from your data but that's not necessarily the case.

You can use a LEFT JOIN back to itself joining on CustomerCode, ApplicationID and Revenue = -Revenue; this effectively finds "purchase" rows that have an associated "refund" row (and vice versa). You can then just filter them off with your WHERE clause

Here's the code I used

DROP TABLE IF EXISTS #Orders
CREATE TABLE #Orders (CustomerCode VARCHAR(1), ApplicationID INT, InvoiceNumber VARCHAR(2), [Date] DATE, Revenue INT)
INSERT INTO #Orders (CustomerCode, ApplicationID, InvoiceNumber, Date, Revenue)
VALUES ('A', 11, 'AA', '2020-01-01', 150),
       ('A', 11, 'AA', '2020-01-02', 200),
       ('A', 11, 'AA', '2020-01-01', 250),
       ('A', 11, 'AA', '2020-01-01', 300),
       ('A', 11, 'BB', '2020-01-05', -150),
       ('A', 11, 'BB', '2020-01-05', -200),
       ('A', 22, 'CC', '2020-01-07', 500),
       ('A', 22, 'DD', '2020-01-07', 700),
       ('A', 11, 'AA', '2020-01-02', 800)

SELECT O.CustomerCode, O.ApplicationID, O.InvoiceNumber, O.Date, O.Revenue
FROM #Orders AS O
LEFT JOIN #Orders AS O2 ON O2.ApplicationID = O.ApplicationID AND O2.CustomerCode = O.CustomerCode AND O.Revenue = -O2.Revenue
WHERE O2.ApplicationID IS NULL

And this is the output:

CustomerCode    ApplicationID   InvoiceNumber   Date        Revenue 
A               11              AA              2020-01-01  250 
A               11              AA              2020-01-01  300 
A               22              CC              2020-01-07  500 
A               22              DD              2020-01-07  700 
A               11              AA              2020-01-02  800