Solved

Separating Debit and Credit at SQL Query.

Posted on 2009-04-08
12
515 Views
Last Modified: 2012-05-06
Hi,

I have 2 tables structures:

Table One (A)
1. TrsNo.
2. AccNoA.
3. TrsId (D/C).

Tables Two (B).
1. TrsNo.
2. AccNoB.
3. Amount (-1000, 1000)

i.e.

Table One                                Table Two

TrsNo  AccNoA   TrsId               TrsNo   AccNoB  Amount
A         11           D                     A         22         100

The result :

TrsId = 'D'

TrsNo  AccNo  Debet     Credit
A         11           100            0
A         22               0        100

TrsId = 'C'

TrsNo  AccNo  Debet     Credit
A         22           100            0
A         11              0         100

If Amount is less than 0 then reverse from Debet to Credit.

How could I do it?

Thank you.


0
Comment
Question by:emi_sastra
  • 6
  • 4
  • 2
12 Comments
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
Sorry, more result.

The result :

TrsId = 'D'

TrsNo  TrsId    AccNo  Debet     Credit
A         D         11           100            0
A         C         22               0        100

TrsId = 'C'

TrsNo  TrsId    AccNo  Debet     Credit
A         D         22           100            0
A         C         11              0         100

If Amount is less than 0 then reverse from Debet to Credit.

How could I do it?

Thank you.
0
 
LVL 3

Expert Comment

by:GarthSnyder
Comment Utility
This schema is a little strange - are you sure you wouldn't be better off with a single (TrsID, AcctA, AcctB, Amount) table?

But assuming you are given this schema... Just use an IF function: IF(Amount < 0, 0, Amount) for the debit and IF(Amount >= 0, Amount, 0) for the credit. That's for the first line of output. Use an analogous pair of IFs for the other account.

This also works in Excel.
0
 
LVL 3

Expert Comment

by:GarthSnyder
Comment Utility
Sorry, the second IF in that example should be IF(Amount < 0, -Amount, 0). But you get the idea.
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
Hi Garth,

Those tables are header and detail tables.

Would you please provide more complete sample to get the result, cause I am not familiar with it?

Thank you.
0
 
LVL 3

Expert Comment

by:GarthSnyder
Comment Utility
If you can post the original SQL that generates your output, I will update it with the IF expressions.
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
The problem is I have idea of doing it, I never do it before querying like it.

Just do if function is simple, but doing the query for different result from 2 tables is different story.

Thank you.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 3

Expert Comment

by:GarthSnyder
Comment Utility
I'm afraid you'll have to be more specific about what you need. You will not get the output you sketched out above (with TrsID labels for each case and separate headers per transaction) from a single query. This is more in the domain of reporting than a database query.

Whatever query generates the fields marked "debit" and "credit", just use the IF() expression in both of those to put the amount value in the proper column.
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
I'm afraid you'll have to be more specific about what you need. You will not get the output you sketched out above (with TrsID labels for each case and separate headers per transaction) from a single query. This is more in the domain of reporting than a database query.
It is possible, I know the powerful of query, not no need using reporting.

Whatever query generates the fields marked "debit" and "credit", just use the IF() expression in both of those to put the amount value in the proper column.
Yes, using If function but not as simple as IF(Amount < 0, 0, Amount).

Thank you.


0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
Comment Utility
Can you check this.
select A.TrsNo,'D' as TrsId ,

       case when A.TrsId = 'D' then A.AccNoA else B.AccNoB end as AccNo,

       case when B.Amount>0 then B.Amount else 0 end as Debit,

       case when B.Amount<0 then B.Amount else 0 end as Credit

  from TableA A join TableB B on A.TrsNo = B.TrsNo

 union all

select A.TrsNo,'C' as TrsId ,

       case when A.TrsId = 'C' then A.AccNoA else B.AccNoB end as AccNo,

       case when B.Amount<0 then B.Amount else 0 end as Debit,

       case when B.Amount>0 then B.Amount else 0 end as Credit

  from TableA A join TableB B on A.TrsNo = B.TrsNo

Open in new window

0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
Hi Sharath,

Let me try first.

My real table structure is different from the sample I provide, just to make it simple.

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
Hi Sharath,

That's great, just one shot for a single record at TableB.

I will open another question for multiple record at TableB.

Thank you very much for your help
   
   

0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
You are welcome. sure, you can open another question and provide the link
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video discusses moving either the default database or any database to a new volume.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

771 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now