question

manuu avatar image
manuu asked

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?
sql-server-2008sql-server-2005sql-server-2008-r2sql-server
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.