How to differentiate between debit and credit account in SQL Server columns?-CodePudding

Here is some sample data:

Transaction_ID	Amount	Credit_Account	Debit_Account	Transaction_Type
TT23023F02B9	200	PKR1000090662010	135483688	Cash deposit
TT23023FX3MZ	1658297	PKR1000023233010	PKR1000099993010	Teller to Teller Transfer
TT23023GPNPS	260000	133519319	PKR1000073160016	Cash Withdrawal
TT23023HLP3P	342500	135482722	PKR1000015443000	Cash Withdrawal
TT23023JGVTC	2046980	PKR1000032342023	PKR1000099992023	Teller to Teller Transfer
TT23023KGGPW	857500	135484137	PKR1000050214016	Cash Withdrawal
TT23023LF6J6	274700	132352909	PKR1000031072030	Cash Withdrawal
TT23023MC2FX	263926	PKR1000051764012	PKR1000099994012	Teller to Teller Transfer
TT23023N37H0	179000	PKR1000031353007	132620326	Cash deposit
TT23023P067S	6400	PKR1000014553002	131623003	Cash deposit
TT23023PZM84	1561200	135478636	PKR1000052572024	Cash Withdrawal
FT2302348TXC	147542	120673138	128787728	AA Loan Payoff

Most of the coulumns are self-explanatory but let explain a little.

There are some transactions going on with unique Transaction_ID.
A user can have multiple accounts, so, Credit_Account and Debit_Account aren't unique for a user(Transaction_ID).
Debit_Account is the account from which the Amount is transferred while Credit_Account is the receiving account.

What I want:

I want to calculate Total_Credit_Amount and Total_Debit_Amount with respect to Accounts in both coulmns.

Distinct Credt/Debit Acc	Total_Credit_Amount	Total_Debit_Amount
abc	amount	amount
xyz	amount	amount

I tried different iterations of CASE statements but all in vain.

Any help would be appreciated!

CodePudding user response：

You can use two subqueries combined and then summarised, as follows:

select 
      Account
    , sum(Credit_Amount) as Credit_amount
    , sum(Debit_Amount) as Debit_amount
from (
    select 
          Credit_Account as Account
        , Amount as Credit_Amount
        , 0 as Debit_Amount
    from SomeData
    union all
    select 
          Debit_Account as Account
        , 0 as Credit_Amount
        , Amount as Debit_Amount
    from SomeData
  ) Step1
group by Account

Alternative not relying on union's column order:

select 
      Account
    , sum(case when CR_DR='CR' then Amount else 0 end) as Credit_amount
    , sum(case when CR_DR='DR' then Amount else 0 end) as Debit_amount
from (
    select 
          Credit_Account as Account
        , CR_DR='CR'
        , Amount as Amount
    from SomeData
    union all
    select 
          Debit_Account as Account
        , CR_DR='DR'
        , Amount as Amount
    from SomeData
  ) Step1
group by Account

Alternative using PIVOT command, based on @June7's idea (thank you):

select 
  Account
, Credit_Amount
, Debit_Amount
from (
    select 
          Credit_Account as Account
        , CR_DR='Credit_Amount'
        , Amount as Amount
    from SomeData
    union all
    select 
          Debit_Account as Account
        , CR_DR='Debit_Amount'
        , Amount as Amount
    from SomeData
    ) Step1
pivot (
    SUM(amount)
    FOR CR_DR IN([Credit_Amount],[Debit_Amount])
) as PT