Group By - Using Absolute Values

Question

I'm trying to display an accounting report where I show total transactions, voids, the transaction fee, and a total amount for each transaction type.

TransactionType  Amount  TransactionCount   TotalAmount
AgentCredit      -$1.00   49                 -$49.00
MailFee          -$1.25   11                 -$13.75
MailFee           $1.25   531                 $663.75
HardCardFee      -$5.00   7                 -$35.00
HardCardFee       $5.00   239                $1,195.00
QuotaHuntFee     -$2.00   1                 -$2.00
QuotaHuntFee      $2.00   202                $404.00

But what I want to display would look like the following:

TransactionType Amount  TransactionCount TotalAmount    TotalTrans Voids
AgentCredit    -$1.00    49               -$49.00         49         0
MailFee         $1.25    520               $650.00        531        11
HardCardFee     $5.00    232               $1,160.00      239        7
QuotaHuntFee    $2.00    201               $402.00        202        1

Would it be possible to group the transaction types using the absolute value of the Amount and calculate the grand total along with the transaction count & void counts?

This is on SQL Server 2014.

Thanks,

Answer 1

I think this does it

declare @T table (nm varchar(20), prc smallmoney, amt int);
insert into @T values  
       ('AgentCredit', -1.00, 49)  
     , ('MailFee', -1.25, 11)
     , ('MailFee', 1.25, 531)
     , ('HardCardFee', -5.00, 7)
     , ('HardCardFee', 5.00, 239)
     , ('QuotaHuntFee', -2.00, 1)
     , ('QuotaHuntFee', 2.00, 202);
with cte as 
(
select t.*, (t.prc * t.amt) as net 
     , count(*) over (partition by t.nm, abs(t.prc)) as cnt
     , row_number() over (partition by t.nm, abs(t.prc) order by t.prc) as rn
     , lag(t.prc) over (partition by t.nm, abs(t.prc) order by t.prc) as prPrc  
     , lag(t.amt) over (partition by t.nm, abs(t.prc) order by t.prc) as prAmt
     , case when lag(t.prc) over (partition by t.nm, abs(t.prc) order by t.prc) <  0 then t.amt - lag(t.amt) over (partition by t.nm, abs(t.prc) order by t.prc) 
            else t.amt 
       end as bal
from @T t 
)
select *, ISNULL(t.prAmt, 0) as void
     , bal*prc as nnet
from cte t 
where t.cnt = 1 
   or t.rn = 2
order by t.nm, t.prc;