Representing null returns as zeros in calculation

  • Thread starter Thread starter Sue Compelling
  • Start date Start date
S

Sue Compelling

Hi

My query purpose is to establish the % of leads (dead ones in this case) PER
campaign PER month against the total leads for each campaign.

In the example below I had 19, 9 and 1 dead lead closed for each campaign in
AUGUST and no dead leads closed for any of the campaigns in SEPTEMBER.

Unfortunately my query uses the 19, 9 and 1 results for AUG in my SEP
calculation. How do I get a null result to read as ZERO?

My query displays like this

Month Campaign CurrentOSLeads CountOfLevel_1 % CM
8 3 mth drop off 217 19 8.76% 83 mth drop off
8 4 mth nil spend 62 9 14.52% 84 mth nil spend
8 Xmas Special 22 1 4.55% 8Xmas Special
9 3 mth drop off 86 19 22.09% 93 mth drop off
9 4 mth nil spend 29 9 31.03% 94 mth nil spend
9 Xmas Special 10 1 10.00% 9Xmas Special


My query is ...

SELECT

[Camp - TotalLeadVolMthly].Month,
[Campaign - TotalDeadOnly].Campaign,
[Camp - TotalLeadVolMthly].CurrentOSLeads,
[Campaign - TotalDeadOnly].CountOfLevel_1,
[countoflevel_1]/[currentosleads] AS [%],
[month] & [camp - totalleadvolmthly.campaign] AS CM

FROM

[Camp - TotalLeadVolMthly]

INNER JOIN

[Campaign - TotalDeadOnly]

ON

[Camp - TotalLeadVolMthly].Campaign = [Campaign - TotalDeadOnly].Campaign

GROUP BY

[Camp - TotalLeadVolMthly].Month,
[Campaign - TotalDeadOnly].Campaign,
[Camp - TotalLeadVolMthly].CurrentOSLeads,
[Campaign - TotalDeadOnly].CountOfLevel_1,
[countoflevel_1]/[currentosleads], [month] & [camp -
totalleadvolmthly.campaign];

TIA
 
Back
Top