Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
divan
Posting Yak Master
153 Posts |
 Posted - 2012-08-21 : 08:40:29
|
| I have two type of policies (insurance policies) one that start with a T and one that start with a M example T10000 and M300000for policies that start with a T I have to calculate the following discountsdiscount_1discount_2discount_3for policies that start with M I have to calculate the above discounts plus additional discounts such asdiscount_1discount_2discount_3discount_9discount_10 discount_11what would be the most efficient way to write the script such that it covers both policies and without having to repeat the code... |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-08-21 : 08:46:45
|
Assuming discounts are given as a fraction, something like this:SELECT UndiscountedPremium, UndiscountedPremium * (1.0-discount_1) * (1.0-discount_2) * (1.0-discount_3) * CASE WHEN PolicyNumber LIKE 'M%' THEN (1.0-discount_9) * (1.0-discount_10) * (1.0-discount_11) ELSE 1.0 ENDFROM TheTable |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-21 : 10:12:36
|
| if discount fields are NULLABLE make sure you wrap them within COALESCE or ISNULL to make NULLs 0 otherwise it will cause entire results to be NULL------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Complicated logic