delete rows by aggregated values

Question

I have a table with detail rows. We want to make transactions for aggregated values from this table. There might be situations, where one row is positive and the another one negative. The aggregate is 0. I want to remove those rows.

Here is my example:

DECLARE @tmp TABLE ( orderid INT
,                    account INT
,                    vatid   INT
,                    amount  DECIMAL(10,2)
,                    vat     DECIMAL(10,2) )

--test values
INSERT @tmp
VALUES ( 10001, 30500, 47, 175.50,  9.20  )
,      ( 10001, 30501, 47, 2010.60, 18.30 )
,      ( 10001, 30501, 47, 147.65,  8.05  )
,      ( 10001, 30502, 47, 321.15,  18.40 )
,      ( 10001, 30502, 47, 13.50,   0.95  )
,      ( 10001, 30510, 40, 15.00,   0.0   )
,      ( 10001, 30510, 40, -15.00,  0.0   )

--all rows
SELECT * FROM @tmp

--aggregate
  --aggregate for account 30510 is 0
SELECT tmp.orderid    
,      tmp.account    
,      tmp.vatid      
,      SUM(tmp.amount) [totalamount]
,      SUM(tmp.vat)    [totalvat]
FROM @tmp tmp
GROUP BY tmp.orderid
,        tmp.account
,        tmp.vatid

--delete rows with aggregated values 0
DELETE tmp
FROM @tmp tmp 
JOIN (
    SELECT ag.orderid
    ,      ag.account
    ,      ag.vatid
    FROM (
        SELECT tmp.orderid    
        ,      tmp.account    
        ,      tmp.vatid      
        ,      SUM(tmp.amount) [totalamount]
        ,      SUM(tmp.vat)    [totalvat]
        FROM @tmp tmp
        GROUP BY tmp.orderid
        ,        tmp.account
        ,        tmp.vatid
        ) ag
    WHERE ISNULL(ag.totalamount,0) = 0
        AND ISNULL(ag.totalvat,0) = 0
    )         tmp2
    ON tmp2.orderid = tmp.orderid
    AND tmp2.account = tmp.account
    AND tmp2.vatid = tmp.vatid

--check rows
SELECT * FROM @tmp

My code works and deletes rows with aggregated values of 0.

But it doesn't look very elegant. Is there a better way to achieve the same result?

Greetings Reto

Answer 1

Using window function

delete tt
from (  select sum(t.vat)    over (partition by t.orderid, t.account, t.vatid) as sumVat
             , sum(t.amount) over (partition by t.orderid, t.account, t.vatid) as sumAmt
          from @tmp t 
     ) tt 
where isnull(tt.sumAmt, 0) = 0 
  and isnull(tt.sumVat, 0) = 0