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:
x
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;
Advertisement
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]