All geek questions in one place

Calculation of commission sales using a sharing schedule

I am trying to derive sales commissions based on a sharing schedule. I have 2 problems:

  • How to write a query without using the built-in subquery
  • How to withdraw the seller and their remaining commission if they did not divide it by 100%.

This is what I have so far:

DECLARE @SALES TABLE ( Id INT, SalesPerson VARCHAR(10), Commission MONEY ) INSERT INTO @SALES VALUES(1,'Amy', 100.00) INSERT INTO @SALES VALUES(2,'Lisa', 200.00) INSERT INTO @SALES VALUES(3,'Joe', 300.00) INSERT INTO @SALES VALUES(4,'Wendy', 400.00) INSERT INTO @SALES VALUES(5,'Mike', 500.00) DECLARE @SHARE_SCHEDULE TABLE ( GIVER INT, TAKER INT, PERCENTAGE DECIMAL(9,2) ) INSERT INTO @SHARE_SCHEDULE VALUES(3, 1, .5) INSERT INTO @SHARE_SCHEDULE VALUES(3, 2, .5) INSERT INTO @SHARE_SCHEDULE VALUES(4, 5, .4) SELECT S.SalesPerson, S.Commission + (SELECT SUM(Commission) FROM @SALES WHERE ID = SS.GIVER) * SS.PERCENTAGE Commission FROM @SALES S JOIN @SHARE_SCHEDULE SS ON SS.TAKER = S.ID

Expected results:

 SalesPerson Commission Amy 250.00 Lisa 350.00 Wendy 240.00 Mike 660.00
+5
source share
5 answers
 with Q as ( select S.*, coalesce(J.share, 0) received, coalesce(K.share, 0) given from SALES S outer apply ( select sum(H.PERCENTAGE * L.Commission) as share from SHARE_SCHEDULE H inner join SALES L on L.Id = H.GIVER where H.TAKER = S.Id ) J outer apply ( select sum(H.PERCENTAGE * L.Commission) as share from SHARE_SCHEDULE H inner join SALES L on L.Id = H.GIVER where H.GIVER = S.Id ) K ) select SalesPerson, (Commission + received - given) TotalCommission from Q where (Commission + received - given) > 0

J is the total amount of the total commission received by the seller.

K is the total amount of the total commission provided by the seller

The intermediate result ( select * from Q ) looks like this:

 Id SalesPerson Commission received given --- ----------- ---------- ------------ ----------- 1 Amy 100.00 150.000000 0.000000 2 Lisa 200.00 150.000000 0.000000 3 Joe 300.00 0.000000 300.000000 4 Wendy 400.00 0.000000 160.000000 5 Mike 500.00 160.000000 0.000000

The final result is as follows:

 SalesPerson TotalCommission (= commission + received - given) ----------- --------------- Amy 250.000000 Lisa 350.000000 Wendy 240.000000 Mike 660.000000

Demo version of registries

+1
source 
 SELECT S.Id, S.SalesPerson, S.Commission + ISNULL(G.GIVEN,0) +ISNULL(T.TAKEN,0) Commission FROM @SALES S LEFT JOIN ( SELECT GIVER, -SUM(PERCENTAGE*S.Commission) GIVEN FROM @SHARE_SCHEDULE SS JOIN @SALES S ON SS.GIVER=S.Id GROUP BY GIVER ) G ON G.GIVER=S.Id LEFT JOIN ( SELECT TAKER, SUM(PERCENTAGE*S.Commission) TAKEN FROM @SHARE_SCHEDULE SS JOIN @SALES S ON SS.GIVER=S.Id GROUP BY TAKER )T ON T.TAKER=S.Id WHERE S.Commission + ISNULL(G.GIVEN,0) >0

See a working demo

+1
source

It must be divided into three parts:

  • Source table SALES
  • Donors with highlighted values
  • Takers with values ​​obtained

Then combine them to get the desired result.

 select S.SalesPerson, (S.Commission --original commision + (ISNULL(tempTaker.Commission, 0)) -- plus recevied as TAKER - (ISNULL(tempGiver.Commission, 0)) -- minus paid as GIVER ) Commission from @SALES s --tempTaker left outer join ( select ss1.TAKER, SUM(ss1.PERCENTAGE * s1.Commission) Commission FROM @SALES S1 inner join @SHARE_SCHEDULE ss1 on s1.Id = ss1.GIVER group by ss1.TAKER ) tempTaker on tempTaker.TAKER = s.id --tempGiver left outer join ( select ss1.GIVER, SUM(ss1.PERCENTAGE * s1.Commission) Commission FROM @SALES S1 inner join @SHARE_SCHEDULE ss1 on s1.Id = ss1.GIVER group by ss1.GIVER ) tempGiver on tempGiver.GIVER = s.id --filter, the final value should be greater than ZERO. where (s.Commission + ISNULL(tempTaker.Commission, 0) - ISNULL(tempGiver.Commission, 0) ) > 0
+1
source

I did this with some CTEs, one for names, the amounts they gave, and the amount they received from others. I also formatted the commission amounts as having two decimal points.

 ;WITH base AS ( SELECT SalesPerson ,Commission ,id FROM @SALES ), commission_given AS ( SELECT S.GIVER ,SUM(base.Commission * s.PERCENTAGE) * -1 commission_given FROM @SHARE_SCHEDULE s INNER JOIN base ON s.GIVER = base.Id GROUP BY S.GIVER, S.PERCENTAGE ), commission_received AS ( SELECT S.taker ,SUM(base.Commission * s.PERCENTAGE)as commission_received FROM @SHARE_SCHEDULE s INNER JOIN base ON s.giver = base.Id GROUP BY S.taker, S.PERCENTAGE ), TOTAL_COMMISSION AS ( SELECT BASE.SalesPerson ,BASE.Commission + ISNULL(cs.commission_given,0) + ISNULL(cr.commission_received,0) AS TOTAL_COMMISSION FROM BASE LEFT OUTER JOIN commission_given AS cs ON BASE.Id = cs.GIVER LEFT OUTER JOIN commission_received cr on base.id = cr.taker ) SELECT SalesPerson ,CAST(TOTAL_COMMISSION AS NUMERIC(10,2)) AS TOTAL_COMMISSION FROM TOTAL_COMMISSION WHERE TOTAL_COMMISSION > 0

working demo: http://rextester.com/live/NWQIR26309

+1
source

I believe this solves your problem:

 SELECT S.SalesPerson, MAX(S.Commission) + Sum(IsNull(ResPlus.Value,0)) - Sum(IsNull(ResSubt.Value,0)) Commission FROM @SALES S LEFT JOIN @SHARE_SCHEDULE SS ON SS.TAKER = S.ID LEFT JOIN @SHARE_SCHEDULE SS2 ON SS2.GIVER = S.ID CROSS APPLY (SELECT SS.PERCENTAGE * SUM(Commission) FROM @SALES WHERE ID = SS.GIVER) ResPlus(Value) CROSS APPLY (SELECT SS2.PERCENTAGE * SUM(Commission) FROM @SALES WHERE ID = SS2.GIVER) ResSubt(Value) group by S.SalesPerson, S.ID having MAX(S.Commission) + Sum(IsNull(ResPlus.Value,0)) - Sum(IsNull(ResSubt.Value,0)) > 0 order by S.ID

Live demo

+1
source

Source: https://habr.com/ru/post/1271165/

More articles:


All Articles