CODEWITHSUNDEEP
sql-servert-sqlreporting-services
Serdia
Serdia

Reputation: 4418

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?

Upvotes: 1

Views: 85

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81930

For your considertion, and as I mentioned, this is one approach with a tier table. Imagine multiple tiers defined supporting numerous requests and reports

Let's assume you have a table Tiers as such

Tier_Grp      Tier_Title    Tier_R1         Tier_R2
Policy Size   < 0           -999999999.00   0.00
Policy Size   0 - 5K         0.00           5000.00
Policy Size   5K - 10K       5000.00        10000.00
Policy Size   10K - 25K      10000.00       25000.00
Policy Size   25K - 50K      25000.00       50000.00
Policy Size   > 50K          50000.00       999999999.00
Policy Size   Total          -999999999.00  999999999.00

Then with 1 simple query, you can get all your data, counts, sums, averages 

Select PolicyType
      ,B.Tier_Title
      ,NbrOfPolicies = count(*)
      ,Premium       = sum(Policy)
FROM   Test_Plaza_ProductionReport  A
Join   Tiers on (B.Tier_Grp='Policy Size' and Premium between B.Tier_R1 and B.Tier_R2 and Premium<B.Tier_R2)
Where  Year(EffectiveDate)=2016
Group  By PolicyType
      ,B.Tier_Title  
Order  By PolicyType
      ,B.Tier_R2

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726509

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

Upvotes: 2

Dariusz Bielak
Dariusz Bielak

Reputation: 415

It is quite possible that you have a negative numbers, but your CASE statement is just for a positive numbers only.

Run the same query, last sum with extra where predicate for premium <0 to check.

Upvotes: 0

Related Questions