select *, case when finallapse = 0 then 0 else ISNULL(totalapse/finallapse,0)*100 end as totallapsepercent,
case when totalpolicycount = 0 then 0 else ISNULL(totalClaimcount/totalpolicycount,0)*100 end as totalclaimfrequency,
case when totpremium = 0 then 0 else ISNULL(totALincurred/TOTPREMIUM,0)*100 end as LossRatio,
case when total_premium= 0 then 0 else ISNULL(net_premium/total_premium,0 ) end as totalpercent,
case when testlapse = 0 then 0 else ISNULL(lapse/testlapse,0)*100 end as Lapsepercent,
case when coverpolicycount = 0 then 0 else ISNULL(net_premium/coverpolicycount,0) end as AvgPremium,
case when coverpolicycount = 0 then 0 else isnull(coverClaimcount/coverpolicycount,0) end as ClaimFrequency,
case when net_premium = 0 then 0 else isnull(coverincurred/net_premium,0)end as CoverLossRatio
from
(select *, sum(NULLIF(lapse,0)) over (partition by brokerno,yoa) as totalapse,
sum(NULLIF (ren+lapse,0)) over (partition by brokerno, yoa) as finallapse,
sum (NULLIF(ren+lapse,0)) over (partition by brokerno, yoa,cover) as testlapse,
sum(NULLIF (claimcount,0)) over (partition by brokerno,yoa) as totalClaimcount,
sum(NULLIF (claimcount,0)) over (partition by brokerno,yoa,cover) as coverClaimcount,
sum(NULLIF(Incurred,0)) over (partition by brokerno,yoa) as totalincurred,
sum(NULLIF(Incurred,0)) over (partition by brokerno,yoa,cover) as coverincurred,
sum(NULLIF(NetPremium,0))over (partition by brokerno,yoa) as TOTPREMIUM,
sum(NULLIF(Policycount,0)) over (partition by brokerno, yoa) as totalpolicycount,
sum(NULLIF(Policycount,0)) over (partition by brokerno, yoa,cover) as coverpolicycount,
sum(NULLIF(netpremium,0))over (partition by brokerno,yoa,cover) as net_premium,
sum (NULLIF(netpremium,0)) over (partition by yoa) as total_premium
from dbo.COMBINEDBROKERSTATS
)k
where brokerno=@brokerno
order by brokerno,yoa,cover
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
by: JCinDEPosted on 2009-01-22 at 10:11:10ID: 23441945
You can't have a multivalue parameter with nothing selected, unfortunately. So just make sure you have an 'All Classes' option in your values list. Usually when I do this I label it '-All Classes-' with a value of '-ALL-' and make it the default selection for the parameter.
Make sure the other dropdown also has a similar selection available such as
Value: '-ALL-'
Label: '-All Schemes-'
If your lists are dataset driven, just add the static options to the results with a union statement.
Then when using the parameters in your dataset you have to do a little magic to accomplish what you want. The SQL is a bit counter-intuitive. See the code snippet.
What the code means is "If All Classes is selected, accept all records. Otherwise only select records whose class was selected." The same logic is applied to the scheme, but I use direct comparison rather than an in() clause since you're not doing multiple select there. You could just as easily do an in() clause in this case.
Select allOpen in new window