Select one column into debit and credit

Question

SELECT TOP 1000 [LEAD_CO_MNE]
      ,CASE
         WHEN CURRENCY <> 'KHR' THEN  AMOUNT_FCY
         ELSE AMOUNT_LCY
         END 'Amount' --I want to split into type of amount debit or crdit
     ,CURRENCY

How could I split the 'Amount' into 2 column debitand credit base on the condition if > 0 credit else debit.

Answer 1

declare @t table (LEAD_CO_MNE int identity, CURRENCY varchar(10), AMOUNT_FCY int, AMOUNT_LCY int);
insert into @t (CURRENCY, AMOUNT_FCY, AMOUNT_LCY)
values ('KHR', 10, 20), ('KHR', -10, -20), ('KHR', 30, 40), ('other', 1, 2);

with cte as
(
SELECT [LEAD_CO_MNE]
      ,CASE
         WHEN CURRENCY <> 'KHR' THEN  AMOUNT_FCY
         ELSE AMOUNT_LCY
         END Amount --I want to split into type of amount debit or crdit
     ,CURRENCY
from @t
)

select [LEAD_CO_MNE], 
       CURRENCY,
       case when Amount > 0 then Amount else 0 end as Credit,
       case when Amount < 0 then Amount else 0 end as Debit
from cte;