Início / dba / Perguntas / 233644
Accepted
kn3l
Asked: 2019-04-02 00:17:41 +0800 CST

Selecione uma coluna em débito e crédito

  • 772
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

Como eu poderia dividir o 'Valor' em 2 colunas debite creditbasear na condição se > 0 crédito ou débito.

sql-server

1 respostas

  1. Best Answer
    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;
    • 1

relate perguntas