T-SQL query to display positive values (deposits) and negative values (withdraw) between accounts-CodePudding

SELECT
  [Trans_Id]
  ,[Transaction_Date]   
  ,[FromAccount_ID]
  ,[ToAccount_ID]
  ,[Amount]
  FROM [Transaction]

Currently all the values are positive in the Amount column. But I want to have, if the transfer is from 93 to 56 then its the amount value should be ve and if the transfer is from 56 to 93 then the amount value should be -ve. some thing like below.

The example I have shown is just between 2 accounts only. But there are more than 10 accounts and I want to be a bit dynamic and automatically assigns ve and -ve values.

Can some one please help to derive the query for this requirement. Thanks

I tried adding extra column with deposit and withdraw values in it and did not work.

CodePudding user response：

There are several ways you could approach this. The one here takes the data and twists it a bit - instead of thinking about a series of transactions between two accounts, it converts each transaction into two separate records - one for each account.

Note that this could be characterised as transforming the data from a single-entry accounting system, to a version of double-entry accounting.

For example, for the first of the records above (93 -> 56 for 4921), we convert it to two records

For account_Id 93, we keep a transaction of type 'transfer out' to 56 of 4921
For account_Id 56, we keep a transaction of type 'transfer in' from 93 of 4921

This is easy enough to do with a UNION ALL where we take each transaction, and adjust the fields as appropriate in each part of the query to convert it to the two records. Note that in the first part of the union, the 'From' account is used as the new Account_ID; in the second part of the union, the 'To' account is the Account_ID.

You can follow along at this db<>fiddle.

SELECT  Trans_Id, 
        Transaction_Date, 
        FromAccount_ID AS Account_ID, 
        N'Transfer out' AS Transaction_Type, 
        ToAccount_ID AS Other_Account_ID,      
        -1 * Amount AS Amount
  FROM  [Transaction]
UNION ALL
SELECT  Trans_Id, 
        Transaction_Date, 
        ToAccount_ID AS Account_ID, 
        N'Transfer in' AS Transaction_Type,       
        FromAccount_ID AS Other_Account_ID, 
        Amount
  FROM  [Transaction];

Usually, this will make most sense if you're interested in a specific account - looking at both transfers in and transfers out. The below looks at the transactions for account 93.

DECLARE @Account_ID int = 93;

SELECT * FROM
(SELECT  Trans_Id, 
        Transaction_Date, 
        FromAccount_ID AS Account_ID, 
        N'Transfer out' AS Transaction_Type, 
        ToAccount_ID AS Other_Account_ID,      
        -1 * Amount AS Amount
  FROM  [Transaction]
UNION ALL
SELECT  Trans_Id, 
        Transaction_Date, 
        ToAccount_ID AS Account_ID, 
        N'Transfer in' AS Transaction_Type,       
        FromAccount_ID AS Other_Account_ID, 
        Amount
  FROM  [Transaction]
) AS SourceTransData
WHERE Account_ID = @Account_ID
ORDER BY Trans_ID DESC;

Results are as follows

Trans_Id  Transaction_Date  Account_ID  Transaction_Type  Other_Account_ID  Amount
12053     2021-09-02        93          Transfer out      56                -4921.0000
12052     2021-09-02        93          Transfer out      56                -83896.0000
10887     2021-04-29        93          Transfer in       56                52398.0000
10886     2021-04-29        93          Transfer in       56                100000.0000
10885     2021-04-29        93          Transfer in       56                391022.0000
10490     2020-01-21        93          Transfer out      56                -350.0000
10489     2020-01-14        93          Transfer in       56                5000.0000

Note that in the above, I have labelled 'transfers in' as positive, while 'transfers out' as negative. This means if you take the total of all transactions for a given account, it will give you the balance. However, feel free to adjust the /- to fit your model (for example, it's quite possible to keep them all positive, and then to get a total you can take the total of 'transfers in' and subtract the total of 'transfers out').

Also remember that your total of all transactions in the whole dataset will be 0, as there is a corresponding negative transaction for each positive one. Conversely, if you decide to keep all values positive, the total of all transactions will be doubled. Therefore, when looking at totals, you should always do some sort of filtering - either by Account_ID or by Transaction_Type.