How To Include Below Subquery Into Store Proc

with cte as ( select trd_nbr,[date],sum(case when txn_typ='A' then abs(amount) else 0 end) amountforA, sum(case when txn_typ='B' then abs(amount) else 0 end) a

Solution 1:

If the transaction types are fixed then it can be easily achieved using pivot. Please try the following:

droptable if exists #temp
select*into #temp
from
(
    select trd_nbr, txn_typ, sum(abs(amount)) total
    from tab t
    groupby trd_nbr, txn_typ
)t
pivot 
(
    sum(total) for txn_typ in ([A], [B], [C])
)pvt

select trd_nbr, casewhen C=A then C else A-C endas Final_C_amount
from #temp

You can check for B and C as well.

Please find the db<>fiddle here.