Event Balance

With event_cost_payments AS
(
SELECT DISTINCT
R.profile_id
,RC.person_id
,cost_payments = ISNULL(SUM(CP.payment_amount) OVER (PARTITION by R.profile_id, RC.person_id),0)
,cost_discounts = ((SELECT SUM(C.amount) FROM evnt_cost C WHERE C.profile_id IN (@event_id))
* (R.discount_code_percentage + R.early_bird_discount_percentage + R.group_discount_percentage))
+ (R.discount_code_amount + R.early_bird_discount_amount + R.group_discount_amount)
FROM evnt_registrant R
LEFT JOIN evnt_registrant_cost RC ON RC.person_id = R.person_id AND RC.profile_id = R.profile_id
LEFT JOIN evnt_registrant_cost_payment CP ON CP.registrant_cost_id = RC.registrant_cost_id
WHERE R.profile_id IN (@event_id)
),

event_costs AS
(
SELECT DISTINCT
E.profile_id
,event_cost = sum(C.amount) over (PARTITION by E.profile_id)
FROM evnt_event_profile E
JOIN evnt_cost C ON C.profile_id = E.profile_id
WHERE E.profile_id IN (@event_id)
),

event_fees_payments AS
(
SELECT DISTINCT
E.profile_id
,R.person_id
,fee_payment = SUM(FP.payment_amount) OVER (PARTITION by E.profile_id, R.person_id)
FROM evnt_event_profile AS E
LEFT JOIN evnt_registrant AS R ON E.profile_id = R.profile_id
LEFT JOIN evnt_registrant_fee AS RF ON RF.person_id = R.person_id
LEFT JOIN evnt_registrant_fee_payment FP ON FP.registrant_fee_id = RF.registrant_fee_id
WHERE E.profile_id IN (@event_id)
),

event_fees AS
(
SELECT DISTINCT
R.profile_id
,R.person_id
,fee_cost = ISNULL(SUM(RF.quantity * F.amount),0) --technically there could be fee discounts but these are rare because they typically lead to unintentionally giving someone double the discount intended
FROM evnt_registrant AS R
LEFT JOIN evnt_registrant_fee AS RF ON RF.person_id = R.person_id
LEFT JOIN evnt_fee F on F.profile_id = R.profile_id
WHERE R.profile_id IN (@event_id)
GROUP BY R.profile_id, R.person_id
),

summary AS
(
SELECT
C.profile_id
,C.event_cost
,CP.person_id
,CP.cost_payments
,CP.cost_discounts
,F.fee_cost
,fee_payment = COALESCE(FP.fee_Payment,0)
FROM event_costs C
LEFT join event_cost_payments CP on CP.profile_id = C.profile_id
LEFT join event_fees F on F.profile_id = C.profile_id and F.person_id = CP.person_id
LEFT join event_fees_payments FP on CP.person_id = FP.person_id
)

SELECT *
,balance = event_cost - cost_payments - cost_discounts + fee_cost - fee_payment
FROM summary