bfuchs
asked on
SQL Query question
Hi Experts,
I have the following query embedded in a store procedure
What is the most efficient way to accomplish it?
I have the following query embedded in a store procedure
SELECT ''Active'' AS Category, COUNT(*) AS [Total #]
FROM dbo.View_EmpStatisticsEmployeesTbl
WHERE ' + @strFilter +
' And EmployeeID in(
Select EmployeeID from TovInfo where FirstDay is not null)
Now I need to add a column next to the count, the third column should display the percentage of column #2 out of the total (total meaning only including the condition of the strFilter, but not the second condition).What is the most efficient way to accomplish it?
ASKER
@Scott,
Trying the below in SSMS
and getting the following error message
Msg 4145, Level 15, State 1, Line 4
An expression of non-boolean type specified in a context where a condition is expected, near 'where'.
Trying the below in SSMS
SELECT 'Active' AS Category, COUNT(ti.EmployeeID) AS [Total #],
ISNULL(CAST(COUNT(ti.EmployeeID) * 100.0 / NULLIF(COUNT(ese.EmployeeID), 0) AS decimal(4, 1)), 0) AS Percentage
FROM dbo.View_EmpStatisticsEmployeesTbl ese
LEFT OUTER JOIN TovInfo ON ti where
ti.EmployeeID = ese.EmployeeID and
ti.FirstDay is not null
WHERE State = 'nj'
HAVING COUNT(ti.EmployeeID) > 0
and getting the following error message
Msg 4145, Level 15, State 1, Line 4
An expression of non-boolean type specified in a context where a condition is expected, near 'where'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent!!
ASKER
Hi Scott,
Perhaps you can also solve the following?
https://www.experts-exchange.com/questions/28671817/SQL-Query-question.html
Perhaps you can also solve the following?
https://www.experts-exchange.com/questions/28671817/SQL-Query-question.html
ASKER
Hi Scott,
Please advise what to do as is the same question just on a diff query.
https://www.experts-exchange.com/questions/28671817/SQL-Query-question.html?anchorAnswerId=40774198#a40774198
(It does not seem like the expert there will ever finish resolving this issue..)
Thanks,
Ben
Please advise what to do as is the same question just on a diff query.
https://www.experts-exchange.com/questions/28671817/SQL-Query-question.html?anchorAnswerId=40774198#a40774198
(It does not seem like the expert there will ever finish resolving this issue..)
Thanks,
Ben
SELECT ''Active'' AS Category, COUNT(ti.EmployeeID) AS [Total #],
ISNULL(CAST(COUNT(ti.Emplo
FROM dbo.View_EmpStatisticsEmpl
LEFT OUTER JOIN TovInfo ON ti where
ti.EmployeeID = ese.EmployeeID and
ti.FirstDay is not null
WHERE ' + @strFilter
--+ ' HAVING COUNT(ti.EmployeeID) > 0 ' --uncomment if you only want to list ese rows that find at least one matching employeeID in TovInfo