Solved

QUERY TO ASSIGN TRANSACTION AMOUNT TO DEBIT OR CREDIT

Posted on 2011-03-10
2
580 Views
Last Modified: 2012-05-11
Hi All,

I have transaction which are :

1. Money Receive.
2. Money Pay.

Query Condition :

1. When Receive then Debet.
2. When Receive and Minus Then Credit

3. When Pay then Credit.
4. When Pay and Minus Then Debit

Below are my code :

CASE WHEN RIGHT(A.TipeTransaksi, 1) = 'D' THEN B.TransactionAmount ELSE 0.00 END AS Debit"
CASE WHEN RIGHT(A.TipeTransaksi, 1) = 'C' THEN B.TransactionAmount ELSE 0.00 END AS Credit"

How to add condition for minus TransactionAmount  to the right place, Debit or Credit?

Thank you.


0
Comment
Question by:emi_sastra
2 Comments
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 500 total points
ID: 35106098
It's a little long, but this would work:


CASE WHEN RIGHT(A.TipeTransaksi, 1) = 'D' AND B.TransactionAmount > 0 THEN B.TransactionAmount
     WHEN RIGHT(A.TipeTransaksi, 1) = 'C' AND B.TransactionAmount < 0 THEN -1 * B.TransactionAmount
     ELSE 0.00
END AS Debit,
CASE WHEN RIGHT(A.TipeTransaksi, 1) = 'C' AND B.TransactionAmount > 0 THEN B.TransactionAmount
     WHEN RIGHT(A.TipeTransaksi, 1) = 'D' AND B.TransactionAmount < 0 THEN -1* B.TransactionAmount
     ELSE 0.00
END AS Credit

Open in new window

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35106634
Hi ryanmccauley,

It works.

Thank you very much for your help.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question