Conversion failed when converting the varchar value '5.00' to data type int.
I am trying to do a count based on certain criteria and I cannot get the criteria correct. - Exclude Account Types: (8) non-member bond redemption; (15) estate restricted; (16) estate unrestricted and (17) dealer direct. - Exclude Accounts with a Warning code 50 and Share ID 01s that have been closed 90 days or more - Excluded deceased - Exclude duplicate social security numbers ;with records as ( select AD.AccountNumber AS ACCOUNT_NUMBER, SD.ShareType AS TYPE, SD.SharePrimeNameSSN AS SSN, AD.AccountPrimeLongName AS NAME, SD.PROCESSDATE AS DATE, row_number() over (partition by SD.sharePrimeNameSSN order by SD.SHARETYPE ASC) N from arcu.ARCUAccountDetailed AD join arcu.ARCUShareDetailed SD on AD.accountnumber = sd.accountnumber AND AD.ProcessDate = SD.ProcessDate where AD.AccountPrimeNameDeathDate IS NULL AND AD.AccountStatus = 'OPEN' AND SD.ShareStatus = 'OPEN' AND (SD.ShareType != '8' AND SD.ShareType != '15' AND SD.ShareType != '16' AND SD.ShareType != '17') AND (((AccountWarningcode1 != '50' AND AccountWarningcode2 != '50' AND AccountWarningcode3 != '50' AND AccountWarningcode4 != '50' AND AccountWarningcode5 != '50' AND AccountWarningcode6 != '50' AND AccountWarningcode7 != '50' AND AccountWarningcode8 != '50' AND AccountWarningcode9 != '50' AND AccountWarningcode10 != '50' AND AccountWarningcode11 != '50' AND AccountWarningcode12 != '50' AND AccountWarningcode13 != '50' AND AccountWarningcode14 != '50' AND AccountWarningcode15 != '50' AND AccountWarningcode16 != '50' AND AccountWarningcode17 != '50' AND AccountWarningcode18 != '50' AND AccountWarningcode19 != '50' AND AccountWarningcode20 != '50') AND (ShareID = '001' AND ShareCloseDate >= (GetDate() - 90))) AND AccountPrimeNameDeathDate is null )) select Count(*) from records where N = 1
The only integer comparison I see outside of `N` is on the ShareID column (unless accountnumber doesn't have the same data type in each table). Is ShareID the column giving the error? When you identify the column with '5.00' (I've assumed ShareId in this snippet) you can use `convert(numeric, ShareId) = 001` which will have an implicit conversion or you can explicitly convert it all the way to int - `convert(int, convert(numeric, ShareId)) = 001` One other approach would be to use numeric for your constant - `ShareId = 001.0`