I have written a script where i am updating commission, when premium > 0, And commision = 0 If totalpremium =0.
UPDATE psc SET psc.Commission = (CASE WHEN ISNULL(pc.TotalPremium,0) > 0
THEN psc.Premium/pc.TotalPremium ELSE 0.00 END) * psc.PolicyCommission
FROM PolicySectionCommissions psc INNER JOIN (
SELECT PolicyCommissionPK, SUM(ISNULL(Premium,0)) TotalPremium From PolicySectionCommissions
GROUP BY PolicyCommissionPK) pc ON psc.PolicyCommissionPK = pc.PolicyCommissionPK
WHERE psc.DataInputLoadCode <> 0
But my requirement is to
we should split commission evenly between the sections. So, First creating a temporary table counting the number of sections, and then according to premium do the split - if we have 0 premium than split evenly, otherwise split according to premium.
Ex. This means that let's say we have two sections for a policy transactions, and the premium is 0, and commission is 1000, Then we need to place a commission of 500 in each section.
PolicySectionCommissions (Table 2)
Can Anyone Help me on this?
Aug 13, 2013 at 05:51 AM