Crazy Numbers being produced - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Crazy Numbers being produced

Author

Topic

NickC
Yak Posting Veteran

68 Posts

Posted - 2010-06-01 : 07:11:41

Hi I am trying to produce an MI Report but struggling to understand what I'm doing.

I'm triyng to pull in number of policies , number of customers and thte totasl premium.

I have this so far

select segment, count(distinct policy), count(distinct customername), sum(premium) from (

select i.insurance_ref as Policy, m.description as segment, Sum(ral.premium_value) as Premium, p.name as customername
from insurance_file i

inner join insurance_file_risk_link rl
on i.insurance_file_cnt = rl.insurance_file_cnt

inner join risk r
on rl.risk_cnt = r.risk_cnt

inner join ri_arrangement ri
on r.risk_cnt = ri.risk_cnt

inner join ri_model m
on ri.ri_model_id = m.ri_model_Id

inner join ri_arrangement_line ral
on ri.ri_arrangement_id = ral.ri_arrangement_id

inner join party p
on i.lead_agent_cnt = p.party_cnt

inner join party_type pt
on p.party_type_id = pt.party_type_id

inner join gis_policy_link pl
on r.risk_cnt = pl.risk_id

inner join plusone_policy_binder pb
on pl.gis_policy_link_id = pb.plusone_policy_binder_id

inner join plusone_general g
on pb.plusone_policy_binder_id = g.plusone_policy_binder_id

inner join policy_status ps
on i.policy_status_id = ps.policy_status_id

where ps.description = 'Current' and i.insurance_ref not in (select insurance_ref from MI_Excluded_Policies)
group by m.description, i.cover_start_date, i.insurance_ref, p.name)

as tabledd

group by segment

--where ps.descripion = 'Current' and i.insurance_ref not in (select insurance_ref from MI_Excluded_Policies)

but its producing crazy numbers and not sure why...

the multiple joins are for other criteria I need to add on once I've got past this point, and then the where comment is to limit fields once Ive got where I need to.

Any help would be fantastic

Kristen
Test

22859 Posts

Posted - 2010-06-01 : 07:45:44

If you have


FROM Customer
     JOIN DevlieryAddresses
         ON X = Y

and the Customer table contains a value for "premium" then

SELECT SUM(Premium)

will add the value for premium for EACH matching row in DevlieryAddresses

so that may be your problem.

What happens if there are two policy_status records for a given insurance_file?


and i.insurance_ref not in (select insurance_ref from MI_Excluded_Policies)

the "IN" test here will be extremely slow if there are large numbers in the list - I would define "large" as being thousands, or possibly only hundreds.

NOT EXISTS or an OUTER JOIN with a WHERE clause statement excluding PK values from that table which are NULL would perform better.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources