Oleg - 1 year ago 52

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 Source

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