How to sum and subtract one column value based on percentage in SQL Server 2008-CodePudding

DECLARE @BalanceTblRec TABLE 
                       (
                           NetAmount decimal(18, 3), 
                           Percentage int, 
                           [Description] nvarchar(max)
                       )
DECLARE @BalanceTblPay TABLE 
                       (
                           NetAmount decimal(18, 3), 
                           Percentage int, 
                           [Description] nvarchar(max)
                       )

INSERT INTO @BalanceTblRec 
VALUES (21, 11, 'ReceiveReceipt'),
       (20, 11, 'ReceiveReceipt'),
       (20, 10, 'ReceiveReceipt'),
       (20, 20, 'ReceiveReceipt'), 
       (10, 10, 'ReceiveReceipt')

INSERT INTO @BalanceTblPay 
VALUES (10, 11, 'PayReceipt'),
       (10, 11, 'PayReceipt'), 
       (10, 2, 'PayReceipt'),
       (5, 15, 'PayReceipt'),
       (30, 10, 'PayReceipt'),
       (20, 10, 'PayReceipt')

;WITH MaPercentage AS
( 
    SELECT
        Percentage, 
        SUM(NetAmount) AS Net, 
        'Receive' AS Flag 
    FROM
        @BalanceTblRec 
    GROUP BY
        Percentage
    UNION ALL
    SELECT 
        Percentage, 
        SUM(NetAmount) AS Net, 
        'Pay' AS Flag 
    FROM
        @BalanceTblPay 
    GROUP BY 
        Percentage
)
SELECT * FROM MaPercentage

enter image description here

Now here I want subtract net from net based on falg, receive - pay based on percentage.

Like this:

Per Net         Flag
-----------------------
10  30.000 - 50 Receive
11  41.000 - 20 Receive
20  20.000      Receive
2   10.000      Pay
15  5.000       Pay

CodePudding user response：

I think this is what you want:

DECLARE @BalanceTblRec TABLE (NetAmount decimal(18,3), Percentage int, [Description] nvarchar(max))
DECLARE @BalanceTblPay TABLE (NetAmount decimal(18,3), Percentage int, [Description] nvarchar(max))

insert into @BalanceTblRec values (21, 11, 'ReceiveReceipt'),(20, 11, 'ReceiveReceipt'),(20, 10, 'ReceiveReceipt'),(20, 20, 'ReceiveReceipt'), (10, 10, 'ReceiveReceipt')
insert into @BalanceTblPay values (10, 11, 'PayReceipt'),(10, 11, 'PayReceipt'),(10, 2, 'PayReceipt'),(5, 15, 'PayReceipt'),(30, 10, 'PayReceipt') ,(20, 10, 'PayReceipt')


;WITH MaPercentage as ( 
select Percentage, sum(NetAmount) as Net, 'Receive' as Flag from @BalanceTblRec group by Percentage
union all
select Percentage, -sum(NetAmount) as Net, 'Pay' as Flag from @BalanceTblPay group by Percentage
)

select 
  Percentage, 
  abs(sum(net)) as SumNet,
  case when sum(net) > 0 then 'Receive' 
  else 'Pay'
  end as Flag
from MaPercentage 
group by Percentage

Just changed the sign in the Pays and sum groupping by percentage.

CodePudding user response：

Another way is to FULL JOIN the receivements with the payments.

;WITH RCV AS (
  select Percentage, sum(NetAmount) as Net
  from @BalanceTblRec
  group by Percentage
)
, PAY AS (
  select Percentage, sum(NetAmount) as Net
  from @BalanceTblPay 
  group by Percentage
)
SELECT 
COALESCE(r.Percentage, p.Percentage) AS Percentage, 
ABS(COALESCE(r.Net, 0) - COALESCE(p.Net, 0)) AS Net, 
(CASE 
 WHEN (COALESCE(r.Net, 0) - COALESCE(p.Net, 0)) < 0 THEN 'Pay'
 ELSE 'Receive'
 END) AS Flag
FROM RCV r
FULL JOIN PAY p ON p.Percentage = r.Percentage