Home How do I need fill two columns according to value of previous column?
Reply: 1

How do I need fill two columns according to value of previous column?

indigo
1#
indigo Published in 2017-12-07 08:54:35Z

I am newbie in mssql(tsql) and need your help. Now I have the table Test that contains information about Type of transactions, Operation Name, PaymentMethod, Currency and so on. Also, there is table Accounts that contains AccountID, PaymentMethod, and Currency. According to Type from Test table and Payment Method and Currency from Accounts table (this table have an AccountID), I need to create table Transaction and fill columns AccountIDTo and AccountIDFrom of this table according to Type of table Test. In addition, I already have Type and PaymentMethod in Transaction table. So,

   If Type is equal 'Exp' then AccountIDFrom = (AccountID from Accounts) and AccountIDTo = 0.
   If Type is equal 'Inc' then AccountIDTo = (AccountID from Accounts) and AccountIDFrom = 0. 

Have anybody an idea how to do it? Need I use loop in this situation? Please, give main logic how to solve it. Thanks

Test table:

Type OperationName    Currency    Account
Exp  Phone            Rub         Card
Inc  Salary           Rub         Purse

Accounts table:

AccountID    PaymentMethod     Currency
1            Card              Rub
2            Purse             Rub

I need Transaction table like:

Type    OperationName    AccountIDFrom     AccountIDTo
Ajay
2#
Ajay Reply to 2017-12-07 10:33:49Z

Try this, I think this may help.

DECLARE @TAB TABLE (TYPE_ VARCHAR(20),OPERATION_NAME VARCHAR(50),CURRENCY VARCHAR(20),ACCOUNT VARCHAR(50))

INSERT INTO @TAB
SELECT 'EXP','PHONE','RUB','CARD'
UNION ALL
SELECT 'INC','SALARY','RUB','PURSE'

DECLARE @ACC TABLE (ACC_ID INT, PAYMENT_MTD VARCHAR(50),CURRENCY VARCHAR(50))

INSERT INTO @ACC
SELECT 1,'CARD','RUB'
UNION ALL
SELECT 2,'PURSE','RUB'


SELECT T.TYPE_,T.OPERATION_NAME
,CASE WHEN T.TYPE_ = 'EXP' THEN A.ACC_ID  ELSE 0 END AS ACCOUNT_FROM
,CASE WHEN T.TYPE_ = 'INC' THEN A.ACC_ID ELSE 0 END AS ACCOUNT_TO 
FROM @TAB T
INNER JOIN @ACC A ON A.PAYMENT_MTD = T.ACCOUNT

output

 TYPE_  OPERATION_NAME  ACCOUNT_FROM    ACCOUNT_TO
  EXP   PHONE               1              0
  INC   SALARY              0              2
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.315423 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO