When I run the following query on its own it runs perfectly:
Select h.branch as BRANCH, h.HHNUM,
CAST(CASE WHEN h.CSDIR_DEP_FL = 'Y' THEN 1 ELSE 0 END as smallint)as Direct_Deposit_Flag
from Touche.dbo.HHSUMM h
where h.PERCHECKSRV > 0
When I add it to a much longer query (using a lot of Cast/Case) it gives me the arithmethic error - is there a better way to write this query or can it be when performing this calc multiple times it fails: full query below:
select m.BRANCH as Branch,
COUNT(m.HHNUM) as Total_HHs,
SUM(m.TOTALACCTS) as Total_Accounts,
(SUM(m.TOTALACCTS) / COUNT(m.HHNUM)) as Accounts_per_HH,
(SUM(m.TOTALSERV) / COUNT(m.HHNUM)) as Services_per_HH,
(SUM(m.AGE) / COUNT(m.HHNUM)) as Average_HH_Age,
SUM(s.SINGLE_SERVICE_HHs) as Single_Service_HHs,
SUM(s.TWO_SERVICE_HHs) as Two_Service_HHs,
SUM(s.THREE_SERVICE_HHs) as Three_Service_HHs,
SUM(s.FOUR_SERVICE_HHs) as Four_Service_HHs,
SUM(m.DEPOSITACC) as Total_Deposit_Accounts,
SUM(m.DEPOSITBAL) as Total_Deposit_Balance,
SUM(m.DEPOSITBAL) / COUNT(m.HHNUM) as Average_HH_Deposit_Balance,
SUM(m.LOANACC) as Total_Loan_Accounts,
SUM(m.LOANBAL) as Total_Loan_Balance,
SUM(m.LOANBAL) / COUNT(m.HHNUM) as Average_HH_Loan_Balance,
SUM(c.Personal_Checking_Accounts) as Personal_Checking_Accounts,
SUM(o.Online_Banking_Flag) as Online_Banking_HHs,
SUM(oc.Online_Banking_Flag) as Checking_HHs_Online_Banking,
SUM(dc.Debit_Card_Flag) as Checking_HHs_Debit_Card,
SUM(cast(dd.Direct_Deposit_Flag as int)) as Checking_HHs_Direct_Deposit,
--CASE WHEN m.CRMSTRATEGY = 'CULTIVATE' THEN COUNT(m.HHNUM) ELSE '' END as CULTIVATE_HHs,
--CASE WHEN m.CRMSTRATEGY = 'RETAIN' THEN COUNT(m.HHNUM) ELSE '' END as RETAIN_HHs,
--CASE WHEN m.CRMSTRATEGY = 'OPTIMIZE' THEN COUNT(m.HHNUM) ELSE '' END as OPTIMIZE_HHs,
--CASE WHEN m.CRMSTRATEGY = 'MAINTAIN' THEN COUNT(m.HHNUM) ELSE '' END as MAINTAIN_HHs,
--CASE WHEN LEFT(m.DEPDECILE_HH,2) = '01' THEN COUNT(m.HHNUM)
--WHEN LEFT(m.DEPDECILE_HH,2) = '02' THEN COUNT(m.HHNUM) ELSE '' END as DEPDECILE_LEVEL
--GENERATIONHH as Generation, DEPDECILE_HH as Deposit_Decile, LNDECILE_HH as Loan_Decile
'07/01/2015' as Date_Updated
from Touche.dbo.HHSUMM m,
(Select h.branch as BRANCH, h.HHNUM,
CASE
WHEN h.TOTALSERV = 1 THEN 1 ELSE 0 END as SINGLE_SERVICE_HHs,
CASE
WHEN h.TOTALSERV = 2 THEN 1 ELSE 0 END as TWO_SERVICE_HHs,
CASE
WHEN h.TOTALSERV = 3 THEN 1 ELSE 0 END as THREE_SERVICE_HHs,
CASE
WHEN h.TOTALSERV >= 4 THEN 1 ELSE 0 END as FOUR_SERVICE_HHs
from Touche.dbo.HHSUMM h) as s,
(Select h.branch as BRANCH, h.HHNUM,
CASE WHEN h.PERCHECKSRV >0 THEN 1 ELSE 0 END as Personal_Checking_Accounts
from Touche.dbo.HHSUMM h) as c,
(Select h.branch as BRANCH, h.HHNUM,
CAST(CASE WHEN h.CSHOMEBKG_FL = 'Y' THEN 1 ELSE 0 END as smallint)as Online_Banking_Flag
from Touche.dbo.HHSUMM h
where h.PERCHECKSRV > 0) as oc,
(Select h.branch as BRANCH, h.HHNUM,
CAST(CASE WHEN h.CSHOMEBKG_FL = 'Y' THEN 1 ELSE 0 END as smallint) as Online_Banking_Flag
from Touche.dbo.HHSUMM h) as o,
(Select h.branch as BRANCH, h.HHNUM,
CAST(CASE WHEN h.CSDBT_FL = 'Y' THEN 1 ELSE 0 END as smallint)as Debit_Card_Flag
from Touche.dbo.HHSUMM h
where h.PERCHECKSRV > 0) as dc,
(Select h.branch as BRANCH, h.HHNUM,
CAST(CASE WHEN h.CSDIR_DEP_FL = 'Y' THEN 1 ELSE 0 END as smallint)as Direct_Deposit_Flag
from Touche.dbo.HHSUMM h
where h.PERCHECKSRV > 0) as dd
where m.HHNUM = s.HHNUM
and m.HHNUM = c.HHNUM
and s.HHNUM = c.HHNUM
and m.BRANCH = s.BRANCH
and m.BRANCH = c.BRANCH
and s.BRANCH = c.BRANCH
and m.HHNUM = oc.HHNUM
and s.HHNUM = oc.HHNUM
and c.HHNUM = oc.HHNUM
and m.BRANCH = oc.BRANCH
and s.BRANCH = oc.BRANCH
and c.BRANCH = oc.BRANCH
and m.HHNUM = o.HHNUM
and s.HHNUM = o.HHNUM
and c.HHNUM = o.HHNUM
and oc.HHNUM = o.HHNUM
and m.BRANCH = o.BRANCH
and s.BRANCH = o.BRANCH
and c.BRANCH = o.BRANCH
and oc.BRANCH = o.BRANCH
and m.HHNUM = dc.HHNUM
and s.HHNUM = dc.HHNUM
and c.HHNUM = dc.HHNUM
and oc.HHNUM = dc.HHNUM
and o.HHNUM = dc.HHNUM
and m.BRANCH = dc.BRANCH
and s.BRANCH = dc.BRANCH
and c.BRANCH = dc.BRANCH
and oc.BRANCH = dc.BRANCH
and o.BRANCH = dc.BRANCH
and m.HHNUM = dd.HHNUM
and s.HHNUM = dd.HHNUM
and c.HHNUM = dd.HHNUM
and oc.HHNUM = dd.HHNUM
and o.HHNUM = dd.HHNUM
and dc.HHNUM = dd.HHNUM
and m.BRANCH = dd.BRANCH
and s.BRANCH = dd.BRANCH
and c.BRANCH = dd.BRANCH
and oc.BRANCH = dd.BRANCH
and o.BRANCH = dd.BRANCH
and dc.BRANCH = dd.BRANCH
group by m.BRANCH
order by 1