x

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?

more ▼

asked Aug 13, 2013 at 05:51 AM in Default

avatar image

manuu
69 3 4 8

(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2188
x2031
x782
x445

asked: Aug 13, 2013 at 05:51 AM

Seen: 1337 times

Last Updated: Aug 13, 2013 at 06:26 AM

Copyright 2017 Redgate Software. Privacy Policy