CODEWITHSUNDEEP
sqlcross-join
Dan
Dan

Reputation: 79

Cross join on subquery

Fee Table

FeeId(PK)managerId    amount    Type    
1        50           100       1
1        50           10000     39
1        50           50000     2
1        50           50000     3
1        50           50000     4

Manager Table

FeeId(FK)Split    managerId
1        70       68

Desired Results:

FeeId    managerId    amount    Type    
1        50           30        1
1        68           70        1
1        50           3000      39
1        68           7000      39
1        50           15000     2
1        68           35000     2
1        50           15000     3
1        68           35000     3
1        50           15000     4
1        68           35000     4

This dataset is just one record, there are many more FeeId's in my data. A cross join would not take this into account. I basically want to cross join each manager based on the feeId.

The amount column is then recalculated to 70,30 for managerid 68,50 respectivly.

How do I do a cross join on each subset: WHERE f.feeId = m.feeId to get the desired results?

Example of cross join with incorrect results since the manager table will have more then 1 fee:

SELECT 
    f.feeId,
    (cast(m.split as decimal) / 100) * f.amount as amount
FROM
    dbo.fee f
    CROSS JOIN dbo.manager m

Upvotes: 1

Views: 14087

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269523

As I understand this problem, you are trying to allocate the amount in fee between the two managers. The following query does this by cross joining an additional table, which is used to choose the data for each row.

select f.feeid,
       (case when n.n = 1 then f.managerid
             when n.n = 2 then m.managerid
        end) as managerid,
       (case when n.n = 1 then f.amount * (100 - m.split)/100
             when n.n = 2 then f.amount * m.split/100
        end) as amount, f.type
from fee f cross join
     manager m cross join
     (select 1 as n union all select 2) as n;

As a comment, this seems like a very unusual data structure.

Upvotes: 3

Hart CO
Hart CO

Reputation: 34774

It seems like this should work:

SELECT   f.feeId, ,f.managerID, (cast(m.split as decimal) / 100) * f.amount as amount, f.type
FROM fee f
JOIN manager m
ON f.FeeID = m.FeeID
AND f.managerID = m.managerID

Upvotes: 1

Related Questions