Finding revenue based on shared ID’s, SQL

I barely know how to ask this question or if this is possible in SQL. I work for a sales company and reps will frequently share accounts with eachother under “Split Codes”. I have a table that documents all of these split codes and the payout rate for each person’s actual rep code.

Split_codes
Split_code, Rep_code, Payout%
100         200       60%
100         201       40%

Which means 60% of any business under split 100 gets paid to rep 200 and 40% gets paid to rep 201.

My sales table is fairly generic with the relevant fields being “ID,Revenue, etc ,etc” with no mention of the ID being a split or normal code.

Revenue
ID,Revenue,date
100, $100,1/1/2020
200, $200,1/5/2020
201, $150,1/5/2020
205, $100,1/5/2020

I have a third table with all of the Rep_codes that are not splits

Rep_Codes
First,Last,RepCode,etc etc 
John, Smith, 200 , ABC,DE

So it is fairly easy to link the Rep_Code table with the Split_Code table and see when each person gets what and the payout rate for any of their splits. The tricky part is i dont know how to distribute the sales numbers reported under the split codes to their owners’ rep_codes in SQL. I can do this in any scripting language by by querying my SQL tables multiple times times and some code to manually add up the sales based on the payout% to each Rep_code, but can i do this within a single SQL query? Do my existing tables even support this? I’m not expecting a complete answer since parts of this are vague. But I need help figuring out how I should even approach the problem. From what I can tell i need to create additional rows for each Rep_code every time I come across an ID present in my Split_codes table with a calculated revenue value based on the information from Split_codes?????

My expected output would be in the format of the sales table. with the split codes not present and distributed out to the appropriate people. and grouped by any date period necessary.

ID, sum(Revenue)
200: $260
201: $190
205: $100

Answer

Assuming that splits are only on base reps, then a left join can be used for the matching and some aggregation:

select coalesce(sc.rep_code, r.id) as repcode,
       rc.first, rc.last, sum(r.revenue * coalesce(sc.payout, 1))       
from revenue r left join
     split_codes sc
     on r.id = sc.split_code left join
     rep_codes rc
     on rc.repcode = coalesce(sc.rep_code, r.id)
group by coalesce(sc.rep_code, r.id), rc.first, rc.last;

Advertisement

Answer