Unable to get division to work on joined subqueries

I am amalgamating queries I run in MS Access to run on SQL Server and whilst results display OK, I can’t get the division to work (it displays 0)

The total seem to calculate OK but as soon as I add Completed.[Completed Account] it results in nil.

Any help would be appreciated.

Here’s my query:

SELECT 
    Removed.[Company Name], Removed.[Removed Accounts], 
    Removed.[Removed Year], 
    Completed.[Completed Accounts], 
    Completed.[Completed Accounts] / (Completed.[Completed Accounts] + Removed.[Removed Accounts]) AS [Conversion Rate]
FROM 
    (SELECT 
         [New Business].[Introducers Code], Introducers.[Company Name], 
         COUNT([New Business].[NB Reference]) AS [Removed Accounts], 
         CASE 
            WHEN [New Business].[Date Completed] IS NULL 
               THEN Year([New Business].[Date Proposed]) 
               ELSE Year([New Business].[Date Completed]) 
         END AS [Removed Year]
     FROM 
         [New Business] 
     INNER JOIN 
         Introducers ON [New Business].[Introducers Code] = Introducers.[Introducers Code]
     WHERE 
         [New Business].[Client Status] = 'Removed'
     GROUP BY 
         [New Business].[Introducers Code], Introducers.[Company Name], 
         CASE 
            WHEN [New Business].[Date Completed] IS NULL 
               THEN Year([New Business].[Date Proposed]) 
               ELSE Year([New Business].[Date Completed]) 
         END) As Removed
LEFT JOIN 
    (SELECT 
         [New Business].[Introducers Code], 
         COUNT([New Business].[Introducers Code]) AS [Completed Accounts], 
         YEAR([Date Completed]) AS [Completed Year]
     FROM  
         [New Business]
     WHERE 
         [New Business].[Client Status] = 'Completed'
     GROUP BY 
         [New Business].[Introducers Code], YEAR([Date Completed])) AS Completed ON (Removed.[Removed Year] = Completed.[Completed Year]) AND (Removed.[Introducers Code] = Completed.[Introducers Code])
WHERE 
    Completed.[Completed Year] IS NOT NULL
GROUP BY 
    Removed.[Company Name], Removed.[Removed Accounts], 
    Completed.[Completed Accounts], Removed.[Removed Year]
ORDER BY 
    Completed.[Completed Accounts] DESC;

Answer

try the following, multiply by 1.0

Completed.[Completed Accounts] * 1.0/(Completed.[Completed Accounts]+Removed.[Removed Accounts]) As [Conversion Rate]

second option is to cast to decimal

cast(Completed.[Completed Accounts] as float)/(Completed.[Completed Accounts]+Removed.[Removed Accounts]) As [Conversion Rate]

Advertisement

Answer