February 24, 2012 at 12:51 pm
Hello,
I'm trying to obtain the overall SUM from a CASE statement within a query I wrote. Below is the query:
SELECT TYPEAS 'Category',
TASKAS 'Task',
PRIORITYAS 'Priority',
CASE
WHEN DUEDATE >= COMPLETEDTHEN 1
ELSE 0
END AS [b]SLAMet[/b],
--(SUM (SLAMet)/COUNT ([WO_NUM]))*100 AS '%TotalMetSLA',
--RIGHT(CONVERT(VARCHAR, CAST(CLSDDATE - OPENDATE AS DATETIME), 108), 12) AS 'SLA Calculation Time',
ELAPSEMINAS'Duration of call (In min)' ,
OPENDATEAS'Opened Date',
CLSDDATEAS'Closed Date'
FROM TASKS
WHERE TYPE IN ('Hardware', 'Software', 'Non-Incident Requests', 'Vendor')
AND OPENDATE BETWEEN GETDATE() -30 AND GETDATE()
GROUP BY TYPE,
TASK,
PRIORITY,
ELAPSEMIN,
OPENDATE,
WO_NUM,
DUEDATE,
COMPLETED,
CLSDDATE
ORDER BY OPENDATE DESC
After executing the query, the message that comes back is SLAMet is an invalid column name.
Any help or information you can provide me with would be much appreciated.
Thank you
February 24, 2012 at 12:53 pm
wrap it into a subquery so you can use the aliases assigned to the calculated/grouped items:
SELECT
( [SLAMet] / ( [WO_NUM] * 1.0 ) / 100 ) AS '%TotalMetSLA',
*
FROM (SELECT
TYPE AS 'Category',
TASK AS 'Task',
PRIORITY AS 'Priority',
CASE
WHEN DUEDATE >= COMPLETED
THEN 1
ELSE 0
END AS [SLAMet],
COUNT([WO_NUM]) AS WOCOUNT,
--(SUM (SLAMet)/COUNT ([WO_NUM]))*100 AS '%TotalMetSLA',
--RIGHT(CONVERT(VARCHAR, CAST(CLSDDATE - OPENDATE AS DATETIME), 108), 12) AS 'SLA Calculation Time',
ELAPSEMIN AS 'Duration of call (In min)',
OPENDATE AS 'Opened Date',
CLSDDATE AS 'Closed Date'
FROM TASKS
WHERE TYPE IN ( 'Hardware', 'Software', 'Non-Incident Requests', 'Vendor' )
AND OPENDATE BETWEEN GETDATE() - 30 AND GETDATE()
GROUP BY
TYPE,
TASK,
PRIORITY,
ELAPSEMIN,
OPENDATE,
WO_NUM,
DUEDATE,
COMPLETED,
CLSDDATE) X
ORDER BY
OPENDATE DESC
Lowell
February 24, 2012 at 1:54 pm
Thank you Lowell,
When I execute the revised query, I'm now receiving a syntax error.
Msg 102, Level 15, State 1, Line 28
Incorrect syntax near 'x'.
Just wondering if I need to add another from to the main query. Should I do the following:
SELECT
SUM([SLAMet])/COUNT([WO_NUM])*100 AS '%TotalMetSLA',
*
(SELECT
TYPE AS 'Category',
TASK AS 'Task',
PRIORITY AS 'Priority',
CASE
WHEN DUEDATE >= COMPLETED THEN 1 ELSE 0
END AS [SLAMet],
COUNT([WO_NUM]) AS WOCOUNT,
--RIGHT(CONVERT(VARCHAR, CAST(CLSDDATE - OPENDATE AS DATETIME), 108), 12) AS 'SLA Calculation Time',
ELAPSEMIN AS 'Duration of call (In min)',
OPENDATE AS 'Opened Date',
CLSDDATE AS 'Closed Date'
FROM TASKS
WHERE TYPE IN ( 'Hardware', 'Software', 'Non-Incident Requests', 'Vendor' )
AND OPENDATE BETWEEN GETDATE() - 30 AND GETDATE()
GROUP BY
TYPE,
TASK,
PRIORITY,
ELAPSEMIN,
OPENDATE,
WO_NUM,
DUEDATE,
COMPLETED,
CLSDDATE) x
FROM TASKS
ORDER BY
OPENDATE DESC
Thanks again!
February 24, 2012 at 2:07 pm
You are missing the from in your select statement. The subquery that you have named x is acting like your table.
SELECT
SUM([SLAMet])/COUNT([WO_NUM])*100 AS '%TotalMetSLA',
* FROM
(
...
) x
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 28, 2012 at 1:21 pm
Thank you Sean.
I added the 'FROM' keyword to the query, and now I'm receiving another error.
Msg 207, Level 16, State 1, Line 33
Invalid column name 'WO_NUM'.
The WO_NUM is a column within the TASKS table. I tried to do this but it doesn't work:
SELECT
SUM([SLAMet])/COUNT([y.WO_NUM])*100 AS '%TotalMetSLA',
* FROM
(SELECT
TYPE AS 'Category',
TASK AS 'Task',
PRIORITY AS 'Priority',
CASE
WHEN DUEDATE >= COMPLETED THEN 1 ELSE 0
END AS [SLAMet],
COUNT([WO_NUM]) AS WOCOUNT,
--RIGHT(CONVERT(VARCHAR, CAST(CLSDDATE - OPENDATE AS DATETIME), 108), 12) AS 'SLA Calculation Time',
ELAPSEMIN AS 'Duration of call (In min)',
OPENDATE AS 'Opened Date',
CLSDDATE AS 'Closed Date'
FROM TASKS
WHERE TYPE IN ( 'Hardware', 'Software', 'Non-Incident Requests', 'Vendor' )
AND OPENDATE BETWEEN GETDATE() - 30 AND GETDATE()
GROUP BY
TYPE,
TASK,
PRIORITY,
ELAPSEMIN,
OPENDATE,
WO_NUM,
DUEDATE,
COMPLETED,
CLSDDATE) x,
TASKS y
WHERE y.WO_NUM = x.WO_NUM
February 28, 2012 at 1:42 pm
I can't find the WO_NUM column in the SELECT part of the subquery.
February 28, 2012 at 1:43 pm
The column is listed within the SUM function, outside of the subquery:
SUM([SLAMet])/COUNT([y.WO_NUM])*100 AS '%TotalMetSLA',
February 28, 2012 at 1:43 pm
And probably better to use a join instead of comma separated tables, using tables like that makes it REALLY easy to accidentally get a cross join.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 28, 2012 at 1:50 pm
The WHERE condition is based on y.WO_NUM and x.WO_NUM . You'll need both. When referring to the missing column in your subquery, I talked about x.WO_NUM.
Sorry for the lees precise answer.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply