Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

QUERY TO ASSIGN TRANSACTION AMOUNT TO DEBIT OR CREDIT

Posted on 2011-03-10
2
Medium Priority
?
604 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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Screencast - Getting to Know the Pipeline
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

564 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