Oleg - 11 months ago 32

SQL Question

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

`select sum(premium)`

from Test_Plaza_ProductionReport

where PolicyType in ('New Business','Renewal','Rewrite')

and Year(EffectiveDate)=2016

Now the sum is

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
```