Calculating sales commissions using a sharing schedule

Question

I'm trying to output sales commissions based on a sharing schedule. I have 2 problems:

1. How to write the query without using an inline subquery
2. How to output the salesperson and their remaining commission if they have not shared 100% of it.

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

Answer 1

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 sum of all shared commission received by a sales person

K is the sum of all shared commission given by a sales person

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

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 looks like:

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

Rextester Demo