I have the following figured out, however what I want to do is almost come up with a new column based on if the row actually exists in the employeeeval column:
SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName FROM Employee e LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u ON u.Department = e.DeptID WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = @.CompanyID) AND (e.FacilityID = @.FacilityID) AND (ev.PersonalScore IS NULL) ORDER BY e.DeptID, e.LastName
so in my select I want to add something like ev.approved which then that brings back either Null or 0. Then based on that create a variable to bring back as a string and if it = 0 make my string say 'In Progress' and if it's Null, say 'Not Started'.
I would imagine I would need a Declare @.Status nvarchar(15) -- but I get lost after that b/c wouldn't I need some sort of way to count throught the rows of my result set and do an IF statement? I can't quite figure this process out, after numerous attempts.
Add this as a column:
CASE WHEN ev.PersonalScore IS NULL THEN 'Not Started' ELSE 'In Progress' END AS Status
And remove AND (ev.personalScore IS NULL) from the WHERE clause.
Like:
SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,CASE WHEN ev.PersonalScore IS NULL THEN 'Not Started' ELSE 'In Progress' END AS Status FROM Employee e LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u ON u.Department = e.DeptID WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = @.CompanyID) AND (e.FacilityID = @.FacilityID) ORDER BY e.DeptID, e.LastName
Of course, that assumes that every employee can only have 0 or 1 records in the employeeeval table. If you want to test if they have 0 or >0 records, then it would be like this:
SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,CASE WHEN ISNULL(EvalCount,0)=0 THEN 'Not Started' ELSE 'In Progress' END AS Status
FROM Employee e
LEFT OUTER JOIN (SELECT COUNT(*) EvalCount,EmployeeID FROM EmployeeEval GROUP BY EmployeeID) ev ON e.EmployeeID = ev.EmployeeID
LEFT OUTER JOIN [User] u ON u.Department = e.DeptID
WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = @.CompanyID) AND (e.FacilityID = @.FacilityID) ORDER BY e.DeptID, e.LastName
|||Gah, I think I finally understand what it is that you want. Questions I have:
Can there be more than 1 row in the EmployeeEval table for an employee?
It seems like someone (The employee) is starting an eval request, and you enter a row into the EmployeeEval table (With approved=0). The supervisor gets notified and they mark approved=1 when they pick up the request for eval. When the eval is completed, you then remove the row from EmployeeEval? Is that correct?
Can there ever be more than 1 row per employee in the eval table? What happens if an unapproved eval is in the table and they request again? Do you deny the request and not make another row? What if it's in progress (approved=1)? Deny and don't enter another row as well?
|||thanks, but I had to place the approved column in place of the personalscore so it looks like this:
SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,
CASE WHEN ev.approved is null THEN 'Not Started' ELSE 'In Progress' END AS Status FROM Employee e
LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u
ON u.Department = e.DeptID
WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = '21') AND (e.FacilityID = '01') and (ev.PersonalScore is null)
ORDER BY e.DeptID, e.LastName
As you stated above they can only have 0 or 1 records that's why I went w/the first one. Thanks for the help.
|||Everytime I get one thing working people keep requesting things I just don't have the experience to complete. I now need the ability w/the forementioned query this: I need to count all employees in each dept and if they match the # in the employeeeval table place in a simple text that says Reviews for Dept. 100% complete.I've tried working ways of using the Count statement, but I'm lost again.|||
SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,
CASE WHEN ev.approved is null THEN 'Not Started' ELSE 'In Progress' END AS Status FROM Employee e
LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u
ON u.Department = e.DeptID
WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = '21') AND (e.FacilityID = '01') and (ev.PersonalScore is null)
ORDER BY e.DeptID, e.LastName
I'm starting to get a wee bit confused on what exactly you want back from the query. I'm having serious doubts that the above query is actually going to return what you want it to, but.. To add your last request you can do it two ways.
Add these to your FROM clause:
LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM Employee) c1 ON (c1.DeptID=e.DeptID)
LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM EmployeeEval JOIN Employee ON (EmployeeEval.EmployeeID=Employee.EmployeeID) c2 ON (c2.DeptID=e.DeptID)
Then add this to your columns:
'Reviews for Dept. ' + CAST(FLOOR(c1.cnt/c2.cnt*100,0) as varchar(3)) + 'complete.' AS DeptComplete
if you want the % to be accurate, or the following if you want it to 100% or some other message if not 100%:
CASE WHEN c1.cnt=c2.cnt THEN 'Reviews for Dept. 100% complete' ELSE 'Not done yet!' END As DeptComplete
And you should end up with something like:
SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,
CASE WHEN ev.approved is null THEN 'Not Started' ELSE 'In Progress' END AS Status,
'Reviews for Dept. ' + CAST(FLOOR(c1.cnt/c2.cnt*100,0) as varchar(3)) + 'complete.' AS DeptComplete
FROM Employee e
LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u
ON u.Department = e.DeptID
LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM Employee) c1 ON (c1.DeptID=e.DeptID)
LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM EmployeeEval JOIN Employee ON (EmployeeEval.EmployeeID=Employee.EmployeeID) c2 ON (c2.DeptID=e.DeptID)
WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = '21') AND (e.FacilityID = '01') and (ev.PersonalScore is null)
ORDER BY e.DeptID, e.LastName
The query is being used to create a dataset, which in turn is used to populate a crystal report. The users, however keep wanting new requests. So I'm forced to do this through my Dataset.
I really don't need a percentage brought back, I just need to know when the #'s match in the employeeeval table and employee table, and say either incomplete or complete.
When I ran your query it tells me 3 errors--
Server: Msg 174, Level 15, State 1, Line 3
The floor function requires 1 arguments.
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near 'c1'.
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near 'c2'.
Try:
SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,
CASE WHEN ev.approved is null THEN 'Not Started' ELSE 'In Progress' END AS Status,
'Reviews for Dept. ' + CAST(FLOOR(c1.cnt/c2.cnt*100) as varchar(3)) + 'complete.' AS DeptComplete
FROM Employee e
LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u
ON u.Department = e.DeptID
LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM Employee) c1 ON (c1.DeptID=e.DeptID)
LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM EmployeeEval JOIN Employee ON (EmployeeEval.EmployeeID=Employee.EmployeeID)) c2 ON (c2.DeptID=e.DeptID)
WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = '21') AND (e.FacilityID = '01') and (ev.PersonalScore is null)
ORDER BY e.DeptID, e.LastName
nope now I'm getting the following:
Server: Msg 8118, Level 16, State 1, Line 1
Column 'Employee.DeptID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'Employee.DeptID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
In the above however, I tried removing e.deptid and still got the same error. Where is it pulling Employee.DeptID from?
Doh! My bad.
SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,
CASE WHEN ev.approved is null THEN 'Not Started' ELSE 'In Progress' END AS Status,
'Reviews for Dept. ' + CAST(FLOOR(c1.cnt/c2.cnt*100) as varchar(3)) + 'complete.' AS DeptComplete
FROM Employee e
LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u
ON u.Department = e.DeptID
LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM EmployeeGROUP BY DeptID) c1 ON (c1.DeptID=e.DeptID)
LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM EmployeeEval JOIN Employee ON (EmployeeEval.EmployeeID=Employee.EmployeeID)GROUP BY DeptID) c2 ON (c2.DeptID=e.DeptID)
WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = '21') AND (e.FacilityID = '01') and (ev.PersonalScore is null)
ORDER BY e.DeptID, e.LastName
Another thing I would like to be able to do--If the number of employees in a specific dept = the number of employees in the Employeeeval table I would like to return something like --Reviews are 100% complete for this dept. I don't need the employee name or anything like what I brought back in the SELECT statement.--For this I realize I'll probably need a new procedure. So I'll work on that and if I need help I'm sure I can find help on here.|||
The CAST(FLOOR(c1/c2*100) AS varchar(3)) is two steps, the first FLOOR(c1/c2*100) takes c1 divides it by c2, multiplies it by 100, and then floor says to remove any fraction, always rounding down. (99.9 becomes 99, etc). Then once we have an integer (0-100), we then say take that integer and make it a varchar(3) which is a variable length string up to 3 bytes/characters long.
As for (SELECT COUNT(*) cnt,DeptID FROM Employee ...
I take the count of records where the DeptID is the same and create a derived table from the result. Specifically, I am calling the count of records 'cnt'. DeptID is the second column taken from Employee.
Think of it this way... If you do:
SELECT COUNT(*),DeptID FROM Employee GROUP BY DeptID and your employee table looks like:
DeptId/{other columns}
1/
1
2
2
3
3
3
Then the result of the query would be:
COUNT(*)/DeptID
2/1
2/2
3/3
Instead of allowing the first column name be "COUNT(*)" since it has no other real name, COUNT(*) cnt says that I want the count, but call the column 'cnt'.
That make any sense?
|||Yes--I just wish I could totally grasp T-SQL--So I'm trying to do the portion of SQL I asked in my last post--is there a way to incorporate all depts in this? so I still show only employees who have ev.PersonalScore is NULL but show the All Departments --Kinda like this:
Dept 1 - Supervisor 1 - EmpName - EmployeeID - Status- DeptStatus
But if the dept has all employees w/value in PersonalScore and the counts match -- put Dept Status = 'Dept has all reviews complete' --See I get lost as to where I put this to try and attempt to make it work.
|||Ok so I got what I wanted for just the Dept. but I would like to incorporate it into my other query so I don't have to run a subreport--b/c that is a pain.select distinct d.[description],
Case when c1.cnt = c2.cnt then 'Reviews are complete' else 'Reviews still in Progress' END AS Status
from department d LEFT OUTER JOIN [user] u ON d.deptid = u.department
LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM Employee GROUP BY DeptID) c1 ON (c1.DeptID=d.DeptID)
LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM EmployeeEval JOIN Employee ON (EmployeeEval.EmployeeID=Employee.EmployeeID) GROUP BY DeptID) c2 ON (c2.DeptID=d.DeptID)
ORDER BY d.[description]
I just wouldn't know how to place this in my SELECT for the previous query b/c I need to use the c1 & c2 with d.DeptID instead of Employee|||
Sure... I can help you, but since I'm not totally sure on what you want out of it, let me explain this part.
Take the last query you have, and run that in management studio, or query analyzer, etc. See the results you get back? You can call it a resultset, or you can call it a table. Technically, I believe it should be called a resultset, which is like a table, but it's ordered. In any case, remove the ORDER BY from it, and you should see that the results don't really change, except that now it's in no particular order anymore.
If you now think of that as being a "table" you can now select from, aggregate, join etc, this is how you can make some very complex queries possible. I can see how you might want to use something like this in different queries. You have two options now. You can take that whole query and put parenthesis around it, and use it just about anywhere you can use a table if you give it an alias. For example...
SELECT myalias.description, myalias.status
FROM (your query here) myalias
WHERE ...
You can also use it in joins like...
SELECT myalias.description,myalias.status
FROM (your query here) myalias
JOIN (some other table) myalias2 ON (some condition)
WHERE ...
You can also make your query a view, which basically saves it so you can reuse it easily like...
CREATE VIEW DepartmentStatusView AS
(your query here)
Then you can use it almost anywhere:
SELECT *
FROM DepartmentStatusView
WHERE ...
However, to be truly useful, you will need to include the DeptID in your query, since that is what you would most likely be using in a join condition, so lets go ahead and build something like that:
CREATE VIEW vw_DepartmentStatus AS
select d.*,Case when c1.cnt = c2.cnt then 'Reviews are complete' else 'Reviews still in Progress' END AS Status
FROM department d
LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM Employee GROUP BY DeptID) c1 ON (c1.DeptID=d.DeptID)
LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM EmployeeEval JOIN Employee ON (EmployeeEval.EmployeeID=Employee.EmployeeID) GROUP BY DeptID) c2 ON (c2.DeptID=d.DeptID)
Now, anytime you want the department status, you can simply join in vw_DepartmentStatus using the DeptID for the ON clause, and you can reference it. Infact, you can simply think of vw_DepartmentStatus as the departments table with an extra "Status" column at the end if that helps.
If you want to add the "Status" on any query that you had before, so long as you were using Department, you can simply change your reference from Department to vw_DepartmentStatus and now you can reference the Status column like going from:
SELECT (some fields)
FROM departments d
JOIN (some stuff)
WHERE (some conditions)
to:
SELECT (some fields),d.status
FROM vw_DepartmentStatus d
JOIN (some stuff)
WHERE (some conditions)
Note the only change was to add d.status as a column you wanted to return and the change from departments to vw_DepartmentStatus
No comments:
Post a Comment