How to calculate Debit and credit using sql server

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

I Hi, Good Morning everyone.

I have a doubt for calculating debit and credit.
I have two table
Table 1 (Voucher): VoucherNo,VoucherName,VoucherDate, Debit.
Table 2 (Receipt):ReceiptNo,ReceiptName,Credit.

I need out put like below

VoucherNo  VoucherName Debit  Credit
001         Purcharse  1000      0
02           sales       0       2500

and so on

This is my query

SQL

SELECT VoucherNo,VoucherName,Debit, NULL as Credit
FROM tbl_Voucher
UNION ALL
SELECT ReceiptNo,ReceiptName,Credit,NULL as Debit
FROM tbl_Receipt

but i don't get proper output

Help me

Posted 27-Dec-15 19:42pm

Balakrishna Kuppam

Updated 28-Dec-15 10:47am

Maciej Los

v3

Add a Solution

Comments

Mehdi Gholam 28-Dec-15 3:47am

... and what is the "correct" output?

[no name] 28-Dec-15 4:04am

Provide data with your tables. Secondly is there any relation between two tables.

Balakrishna Kuppam 28-Dec-15 5:34am

Hi, Manas_Kumar
in both table id is common column

Sinisa Hajnal 28-Dec-15 5:26am

You should use LEFT JOIN or INNER JOIN to join the tables. You'll have to have a field in common with two tables through which you can connect particular receipt with the voucher.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ZurdoDev · Accepted Answer · 2015-12-28T02:03:00

A join would be better assuming that one table is the "main" table. For example, if all records will exist in tbl_Receipt then do something like this:

For example:

SQL

SELECT r.VoucherNo, r.Credit, v.Debit
FROM tbl_Receipt r
LEFT JOIN tbl_Voucher v ON r.ID = v.ID

Or, you could wrap your existing SQL into a SELECT statement:

SQL

SELECT x.VoucerhNo, x.VoucherName, SUM(x.Debit), SUM(x.Credit)
FROM (
  SELECT VoucherNo,VoucherName,Debit, NULL as Credit
  FROM tbl_Voucher
  UNION ALL
  SELECT ReceiptNo,ReceiptName,Credit,NULL as Debit
  FROM tbl_Receipt
) x 
GROUP BY x.VoucherNo, x.VoucherName

The join will likely be a better approach.