Calculate commission using Premium,Calculate commision using Premium..
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 SELECT @@ROWCOUNT 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. **Table structure** **PolicyCommissions**(Table 1) PolicyCommissionPK OrigPolicyCommissionPK SourceSystemID PolicyTransPK CommissionIntermediaryPK Commission CommissionDaily **PolicySectionCommissions** (Table 2) PolicySectionCommissionPK PolicySectionPK PolicyCommissionPK PolicyTransPK SourceSystemID CommissionIntermediaryPK PolicyCommission PolicyCommissionDaily Premium Commission CommissionDaily Can Anyone Help me on this?