data is repeating, one transaction should apear only once. help me with this, ThanX

Question

0.00/5 (No votes)

See more:

Select d.Transaction_No,d.Amount_Debit,d.Debit_Date,d.Total_Amount_Debit,c.Transaction_No,c.Amount_Credit,c.Credit_Date,c.Total_Amount_Credit from Account_Debit d left join Account_Credit c on c.Account_No=d.Account_No and  d.Account_No='1234-5';

TransactionNo
1	    6565	2013-01-04	883441	1	54	2013-01-04	54
1	    6565	2013-01-04	883441	2	54	2013-01-04	108
1	    6565	2013-01-04	883441	3	6565	2013-01-04	6673
1	    6565	2013-01-04	883441	4	6756	2013-01-04	13429
2	    65654	2013-01-04	949095	1	54	2013-01-04	54
2	    65654	2013-01-04	949095	2	54	2013-01-04	108
2	    65654	2013-01-04	949095	3	6565	2013-01-04	6673
2	    65654	2013-01-04	949095	4	6756	2013-01-04	13429
3	    65567	2013-01-04	1014662	1	54	2013-01-04	54
3	    65567	2013-01-04	1014662	2	54	2013-01-04	108
3	    65567	2013-01-04	1014662	3	6565	2013-01-04	6673
3	    65567	2013-01-04	1014662	4	6756	2013-01-04	13429
4	    6564	2013-01-04	1021226	1	54	2013-01-04	54
4	    6564	2013-01-04	1021226	2	54	2013-01-04	108
4	    6564	2013-01-04	1021226	3	6565	2013-01-04	6673
4	    6564	2013-01-04	1021226	4	6756	2013-01-04	13429

Posted 6-Jan-13 19:59pm

zeshanazam

Add a Solution

Comments

Aarti Meswania 7-Jan-13 2:12am

what output you want?

zeshanazam 7-Jan-13 2:13am

each transaction should appear only one time...
e.g
Transaction no
1
2
3
4

Aarti Meswania 7-Jan-13 2:15am

you mean Account_Debit and account credit same transaction nos should together?
you want output like below?
1 6565 2013-01-04 883441 1 54 2013-01-04 54
2 65654 2013-01-04 949095 2 54 2013-01-04 108
3 65567 2013-01-04 1014662 3 6565 2013-01-04 6673
4 6564 2013-01-04 1021226 4 6756 2013-01-04 13429

zeshanazam 7-Jan-13 2:16am

Exactly like this :)

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)

Aarti Meswania · Accepted Answer · 2013-01-06T20:18:00

Solution 1

you have missing to join transaction nos. use below query

SQL

Select d.Transaction_No,d.Amount_Debit,d.Debit_Date,d.Total_Amount_Debit,c.Transaction_No,c.Amount_Credit,c.Credit_Date,c.Total_Amount_Credit 
from Account_Debit d 
left join Account_Credit c on c.Account_No=d.Account_No and d.Transaction_No=c.Transaction_No
where d.Account_No='1234-5';

Happy Coding!
:)

Posted 6-Jan-13 20:18pm

Aarti Meswania

Comments

zeshanazam 7-Jan-13 2:21am

what if i do not want to include transaction no... ??

Aarti Meswania 7-Jan-13 2:23am

it will give you desired output

you can compare that condition inside where clause also

Select d.Transaction_No,d.Amount_Debit,d.Debit_Date,d.Total_Amount_Debit,c.Transaction_No,c.Amount_Credit,c.Credit_Date,c.Total_Amount_Credit
from Account_Debit d
left join Account_Credit c on c.Account_No=d.Account_No
where d.Account_No='1234-5' and d.Transaction_No=c.Transaction_No;

zeshanazam 7-Jan-13 2:24am

when i exclude transaction column, it again produce repeating results..
Select d.Amount_Debit,d.Debit_Date,d.Total_Amount_Debit,c.Amount_Credit,c.Credit_Date,c.Total_Amount_Credit
from Account_Debit d
left join Account_Credit c on c.Account_No=d.Account_No
where d.Account_No='1234-5';

Aarti Meswania 7-Jan-13 2:27am

do not exclude it because you want results that have same account nos and transaction nos also.

so, use transaction no. condition in left join as I do in my answer :)

zeshanazam 7-Jan-13 2:29am

thanx, i want to add last values of 2nd and 4th column and result in last column, how to access column of crystal reports in c#

Aarti Meswania 7-Jan-13 2:31am

welcome! :)
Glad to help you! :)

I can not understand last values of 2nd and 4th column
can you give example records? so I can help you for that

zeshanazam 7-Jan-13 2:34am

column1 column2 sum
23 32 78
34 33
45 34
56 22

Aarti Meswania 7-Jan-13 2:39am

you want this in crystal report then
Create formula field name it "sum_of_cols"

and dragdrop fields you want to sum
e.g
{table.col1} + {table.col2}

now save

and dragdrop this new field "sum_of_cols" on report

Aarti Meswania 7-Jan-13 2:42am

visit this link, it have do multiplication of column, it have explain with images

http://www.mindstick.com/Articles/ad1bda11-7a4c-4760-9df3-987f46b99029/?Formula%20Field%20in%20Crystal%20Report

zeshanazam 7-Jan-13 2:47am

ThanX alot. :)

Aarti Meswania 7-Jan-13 2:48am

My pleasure! :)
Glad to help you! :)