Good Afternoon,
We have two tables:
ex. source (email campaigns by source)
[table]
[tr]
[td]source_code[/td][td]Description[/td][td]startDate[/td][/tr]
[tr][td]PARNER123[/td][td]Product A to Partner123[/td][td]2015-01-01[/td][/tr]
[tr][td]PARTNER456[/td][td]Product B to Partner456[/td][td]2015-01-02[/td]
[/tr]
[/table]
ex. orders (orders by customer)
[table]
[tr]
[td]email[/td][td]source_code[/td][td]grandtotal[/td][td]product[/td][/tr]
[tr][td][email protected][/td][td]PARNER123[/td][td]1.00[/td][td]Product A [/td][/tr]
[tr][td][email protected][/td][td]PARNER123[/td][td]1.00[/td][td]Product A [/td][/tr]
[tr][td][email protected][/td][td]PARTNER456[/td][td]2.00[/td][td]Product B[/td][/tr]
[tr][td][email protected][/td][td]PARNER456[/td][td]2.00[/td][td]Product B [/td][/tr]
[/table]
I need to find all customers for each source code (by email) then determine the total revenue for that group of emails that is NOT tied to that particular source code.
For instance, by the example above, if PARTNER123 is the code, then customer1 and customer 2 ordered by that code. But customer 1 also ordered by PARTNER456. So the revenue of customer 1 that is NOT tied to PARTNER123 would equal $2.00. If there was more than one customer that had alternative revenue, I would want to sum it all and display, like in the below example;
PARTNER 123 has order revenue of $2.00 total , and customers have revenue for other codes in total of $2.00.
Order revenue of $2.00 is the order on PARTNER123 for customer1 and customer2, while the alternative revenue is customer2 order on PARTNER456 for $2.00.
Unfortunately I have tried looping the results, and also adding the amounts to an array and summing up, and also inserting to a temp table, but I can’t seem to get the totals to come out correctly. I would appreciate some guidance here.