Problem converting Access SQL query to T-SQL 2005
I have a SQL Server 2005 database. One of my co-workers has put a MS Access front-end on it, because of the ease of designing queries in Access. I need to convert an existing query from Access SQL to T-SQL to put it into production. So far, I have removed all the obvious differences between the 2, such as replacing IIF with CASE and UCASE$ with UPPER, but still have errors. According to the errors generated, apparently my alias fields are not being created correctly, as I get an error on line 5 that my Department_Essential field does not exist. Does anyone have any suggestions?
SELECT dbo.vwDisplayUserListAllFields.DEPT_DESC,
SUM(CASE WHEN UPPER(Essential_Code)='D' THEN 1 ELSE 0 END) AS 'Department_Essential',
SUM(CASE WHEN UPPER(Essential_Code)='E' THEN 1 ELSE 0 END) AS 'EOC_Essential',
COUNT(dbo.vwDisplayUserListAllFields.UserID) AS 'Total_Employees',
([Department_Essential]/[Total_Employees]*100) AS [%Department_Essential],
([Department_Essential]/[Total_Employees]*100) AS [%EOC_Essential]
FROM dbo.vwDisplayUserListAllFields
GROUP BY dbo.vwDisplayUserListAllFields.DEPT_DESC
ORDER BY dbo.vwDisplayUserListAllFields.DEPT_DESC
Software/Hardware used:
Software/Hardware used:
ASKED:
June 3, 2008 3:41 PM
UPDATED:
June 4, 2008 3:23 PM
Answer Wiki:
Department_Essential is a <b>column alias</b>, and column aliases are not allowed to be referenced in the same SELECT clause
(well, they are allowed in Access, and there's the source of your problem)
so just "push down" the calculations into a subquery:
<pre>SELECT DEPT_DESC
, Department_Essential
, EOC_Essential
, Total_Employees
, 100.0 * Department_Essential / Total_Employees
AS [%Department_Essential]
, 100.0 * EOC_Essential / Total_Employees
AS [%EOC_Essential]
FROM (
SELECT DEPT_DESC
, SUM(CASE WHEN UPPER(Essential_Code) = 'D'
THEN 1
ELSE 0 END) AS Department_Essential
, SUM(CASE WHEN UPPER(Essential_Code) = 'E'
THEN 1
ELSE 0 END) AS EOC_Essential
, COUNT(UserID) AS Total_Employees
FROM dbo.vwDisplayUserListAllFields
GROUP
BY DEPT_DESC
) AS d
ORDER
BY DEPT_DESC </pre>
To see all answers submitted to the Answer Wiki: View Answer History.
That works great! Thank you for your help. The only thing I need to work on now is converting the percentages to whole numbers, i.e. 88, rather than 88.03….. I removed the .0 after the multiplication by 100, and ended up with 11% and 88% – not quite 100%. I’ll work on that. Again, thanks for your help.