Oleg Oleg - 5 months ago 12
SQL Question

Why do I have different result by using CASE statement?

I am using CASE statement to calculate Premium and result is way off if I just use SELECT SUM statement. Why would that be?

select
SUM(CASE WHEN Premium > 0 and Premium <= 5000 AND Year(EffectiveDate)=2016 AND PolicyType = 'New Business' THEN Premium ELSE 0 END) as '0-5K_WP',
SUM(CASE WHEN Premium > 5000 and Premium <=10000 AND Year(EffectiveDate)=2016 AND PolicyType = 'New Business' THEN Premium ELSE 0 END) AS '5K-10K_WP',
SUM(CASE WHEN Premium > 10000 and Premium <= 25000 AND Year(EffectiveDate)=2016 AND PolicyType = 'New Business' THEN Premium ELSE 0 END) AS '10K-25K_WP',
SUM(CASE WHEN Premium > 25000 and Premium <=50000 AND Year(EffectiveDate)=2016 AND PolicyType = 'New Business' THEN Premium ELSE 0 END) AS '25K-50K_WP',
SUM(CASE WHEN Premium > 50000 AND Year(EffectiveDate)=2016 AND PolicyType = 'New Business' THEN Premium ELSE 0 END) AS '>50K_WP'
FROM Test_Plaza_ProductionReport

union all
select
SUM(CASE WHEN Premium > 0 and Premium <= 5000 AND Year(EffectiveDate)=2016 AND PolicyType = 'Renewal' THEN Premium ELSE 0 END) as '0-5K_WP',
SUM(CASE WHEN Premium > 5000 and Premium <=10000 AND Year(EffectiveDate)=2016 AND PolicyType = 'Renewal' THEN Premium ELSE 0 END) AS '5K-10K_WP',
SUM(CASE WHEN Premium > 10000 and Premium <= 25000 AND Year(EffectiveDate)=2016 AND PolicyType = 'Renewal' THEN Premium ELSE 0 END) AS '10K-25K_WP',
SUM(CASE WHEN Premium > 25000 and Premium <=50000 AND Year(EffectiveDate)=2016 AND PolicyType = 'Renewal' THEN Premium ELSE 0 END) AS '25K-50K_WP',
SUM(CASE WHEN Premium > 50000 AND Year(EffectiveDate)=2016 AND PolicyType = 'Renewal' THEN Premium ELSE 0 END) AS '>50K_WP'
FROM Test_Plaza_ProductionReport

union all
select

SUM(CASE WHEN Premium >0 and Premium <= 5000 AND Year(EffectiveDate)=2016 AND PolicyType = 'Rewrite' THEN Premium ELSE 0 END) as '0-5K_WP',
SUM(CASE WHEN Premium > 5000 and Premium <=10000 AND Year(EffectiveDate)=2016 AND PolicyType = 'Rewrite' THEN Premium ELSE 0 END) AS '5K-10K_WP',
SUM(CASE WHEN Premium > 10000 and Premium <= 25000 AND Year(EffectiveDate)=2016 AND PolicyType = 'Rewrite' THEN Premium ELSE 0 END) AS '10K-25K_WP',
SUM(CASE WHEN Premium > 25000 and Premium <=50000 AND Year(EffectiveDate)=2016 AND PolicyType = 'Rewrite' THEN Premium ELSE 0 END) AS '25K-50K_WP',
SUM(CASE WHEN Premium > 50000 AND Year(EffectiveDate)=2016 AND PolicyType = 'Rewrite' THEN Premium ELSE 0 END) AS '>50K_WP'
FROM Test_Plaza_ProductionReport


The sum is gonna be 13,286,473 But now if I use this:

select sum(premium)
from Test_Plaza_ProductionReport
where PolicyType in ('New Business','Renewal','Rewrite')
and Year(EffectiveDate)=2016


Now the sum is 11,993,445
Its two million off!! How is that possible?

Answer

It looks like you 've got some records with negative premium among your policy records. Adding a condition to the short query to reject such negative-premium records should make your numbers match.

If you would like to take negative premiums into account, add an extra "bucket" for them, i.e.

select 
        SUM(CASE WHEN Premium < 0 AND Year(EffectiveDate)=2016 AND PolicyType = 'Renewal' THEN Premium ELSE 0 END) as 'Negative_WP',
        SUM(CASE WHEN Premium > 0 and Premium <= 5000 AND Year(EffectiveDate)=2016 AND PolicyType = 'Renewal' THEN Premium ELSE 0 END) as '0-5K_WP',
        SUM(CASE WHEN Premium > 5000 and Premium <=10000 AND Year(EffectiveDate)=2016 AND PolicyType = 'Renewal' THEN Premium ELSE 0 END) AS '5K-10K_WP',
        SUM(CASE WHEN Premium > 10000 and Premium <= 25000 AND Year(EffectiveDate)=2016 AND PolicyType = 'Renewal' THEN Premium ELSE 0 END) AS '10K-25K_WP',
        SUM(CASE WHEN Premium > 25000 and Premium <=50000 AND Year(EffectiveDate)=2016 AND PolicyType = 'Renewal' THEN Premium ELSE 0 END) AS '25K-50K_WP',
        SUM(CASE WHEN Premium > 50000 AND Year(EffectiveDate)=2016 AND PolicyType = 'Renewal' THEN Premium ELSE 0 END) AS '>50K_WP'
FROM    Test_Plaza_ProductionReport