| Author |
Topic |
|
velvettiger
Posting Yak Master
115 Posts |
 Posted - 2010-01-28 : 15:22:21
|
I have a table called Replacement_2 which look like below"Receiver","Donor","receiverspend""52","94",2.00"19","51",3.00"5","17",4.00"10","81",5.00"26","46",6.00"66","84",7.00"88","87",8.00"91","203",9.00"41","70",10.00 The spend shown in the above table, is money spent by the receiver cardnumbers not the donors cardnumbers I would like to include the spend of the donors. So the result set should look like this "Receiver","Receiver_Spend","Donor","Donor_Spend""52",2.00,"94",87.05"19",3.00,"51",34.66"5",4.00,"17",22.45"10",5.00,"81",90.04"26",6.00."46",567.78"66",7.00,"84",246.35"88",8.00,"87".56.78"91",9.00,"203",4225.80"41",10.00,"70",43.06 And the spend for the donors and receivers are found in a table called Replacement_2In knowing all of this, below you would see the code that I came up with. The donor spend and receiver spend came up incorrect. Any input would be greatly appreciated :)In the below code I have this statement where card_no != d.donor_card_no in red because I am only suppose to consider those persons whose donor cardnumber and the receiver cardnumber are differentselect ReceptorCardnumber , receptor_firstname , receptor_lastname , sum(files.tran_value)Total_Receptor_Spend , DonorCardnumber , Donor_firstname , Donor_lastname , sum(files.Donor_Spend)Total_Donor_Spend from ( select distinct c.cardnumber as ReceptorCardnumber ,card_no as ReceptorCardnumber_RMS ,c.firstname as receptor_firstname ,c.lastname as receptor_lastname ,d.tran_value ,data.donor_card_no as DonorCardnumber ,data.firstname as Donor_firstname ,data.lastname as Donor_lastname ,data.tran_value as Donor_Spend from Replacement_2 d inner join customer c on c.cardnumber= d.card_no inner join ( select distinct d.donor_card_no ,firstname ,lastname ,tran_value from Replacement_2 d inner join customer c on c.cardnumber= d.card_no )data on data.donor_card_no=d.donor_card_no where card_no != d.donor_card_no )as Files group by ReceptorCardnumber , receptor_firstname , receptor_lastname , DonorCardnumber , Donor_firstname , Donor_lastname order by ReceptorCardnumber |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-01-28 : 16:23:15
|
| Can you show some sample data from Replacement_1 and Replacement_2that matches with the expected output that you provided |
 |
|
|
velvettiger
Posting Yak Master
115 Posts |
Posted - 2010-01-28 : 20:16:41
|
No Problem,So below is what the replacement_2 table looks like"receivercardnumber","donorcardnumber","receiverspend""52","94",2.00"19","51",3.00"100","52",4.00"10","81",5.00"26","46",6.00"66","84",7.00"88","87",8.00"91","26",9.00"41","70",10.00 Below is what the result is suppose to be"receivercardnumber","donorcardnumber","receiverspend","donorspend""52","94",2.00,0"19","51",3.00,0"100","52",4.00,2.00"10","81",5.00,0.00"26","46",6.00,0.00"66","84",7.00,0.00"88","87",8.00,0.00"91","26",9.00.6.00"41","70",10.00,0.00 The items in red and blue shows how the script is suppose to work |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-30 : 00:27:05
|
| Where will you get donor spend values from? Whats the business rule for calculating it |
|
|
|
velvettiger
Posting Yak Master
115 Posts |
Posted - 2010-01-30 : 16:19:36
|
| Okay so the donors spend is calculated like followed In the first line of my example you can see that the receiver cardnumber is 52, the donor cardnumber is 94, receiver spend is $2.00 and the donor spend is 0 because when u look through the example the donor cardnumber is n0t found in the the reciver cardnumber coloumn. Now in the 3rd line 52 which is the receiver cardnumber in line 1, is found in the donor cardnumber field. The spend for receiver cardnumber is $2, so the donor spend is now $2.00 for cardnumber 100. |
|
|
|
velvettiger
Posting Yak Master
115 Posts |
Posted - 2010-01-31 : 09:57:48
|
| any ideas? |
|
|
|
|
|
|
Getting totals for 2 cardnumber coloumns