Stored Procedure Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Stored Procedure Problem

Author

Topic

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-11-03 : 05:04:52

Hi i have tables - Employee_skill, Rating, Skill, Employee, Employee_Project,
Project

as shown below

Employee_skill

Skill_NBR Employee_nbr supervisor rating Date
1 1 3 01/01/2007
2 1 4 08/01/2007
3 5 2 02/10/2007
12 2 4 02/10/2007
12 2 3 02/10/2007
5 8 4 01/08/2008
3 1 4 25/10/2008
2 1 5 25/10/2008

Rating
Number Name
1 No Experience
2 Beginner
3 Intermediate
4 Proficient
5 Expert

Skill
Number Name
1 .Net
2 SQL
3 Java
4 Manual Testing
12 Siebel

Employee

Number Name
1 Jhon
2 Smith
3 Vernoica
4 Sing
5 Sony

Employee_Project

Employee_NBR Project_NBR
1 1
2 1
3 1
4 2
5 2

Project

Number Name Start_Date End_DT

1 PIS 10/1/2008 NULL
2 G441 2/15/2007 10/5/2008

The above data states that Project G441 is finished and the employees who were allocated to that project are now on bench and the other 3 employees are on job(project).

Now to the stored Procedure, I would get From Date and to date as Input parameters

And I need to display the data as below

SKILL No.of emp with this skill set current job total employees on bench
.Net 1 1 0
SQL 1 1 0
Java 1 1 1
Siebel 1 1 0

Can anyone help me thru this Stored Procedure.

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 06:03:09

[code]SELECT s.Skill,
COUNT(DISTINCT es.Employee_nbr ) AS TotalEmployeeswithSkill,
COUNT(DISTINCT CASE WHEN p.Number IS NOT NULL THEN es.Employee_nbr ELSE NULL END ) AS ToatlEmployeesCurrentlyInProj,
COUNT(DISTINCT CASE WHEN p.Number IS NULL THEN es.Employee_nbr ELSE NULL END ) AS ToatlEmployeesInBench

FROM Employee_skill es
INNER JOIN Rating r
ON r.Number =es.rating
INNER JOIN Skill s
ON s.Number =es.Skill_NBR
INNER JOIN Employee e
ON e.Number =es.Employee_nbr
INNER JOIN Employee_Project ep
ON ep.Employee_NBR = es.Employee_nbr
LEFT JOIN Project p
ON p.Number =ep.Project_NBR
AND p.End_DT IS NOT NULL
GROUP BY s.Skill[/code]

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-11-03 : 08:45:23

hey thanks vishakh... but i've slightly modified it to p.END_DT IS NULL in the second case statement which actually gave me a proper result.
Thanks a lot for the needful. I need to insert more data and check it again. Will get back if i wont get the desired output.

thanks again

quote:

Originally posted by visakh16

SELECT s.Skill,
COUNT(DISTINCT es.Employee_nbr ) AS TotalEmployeeswithSkill,
COUNT(DISTINCT CASE WHEN p.Number IS NOT NULL THEN es.Employee_nbr ELSE NULL END ) AS ToatlEmployeesCurrentlyInProj,
COUNT(DISTINCT CASE WHEN p.Number IS NULL THEN es.Employee_nbr ELSE NULL END ) AS ToatlEmployeesInBench

FROM Employee_skill es
INNER JOIN Rating r
ON r.Number =es.rating 
INNER JOIN Skill s
ON s.Number =es.Skill_NBR
INNER JOIN  Employee e
ON e.Number =es.Employee_nbr 
INNER JOIN Employee_Project ep
ON ep.Employee_NBR = es.Employee_nbr 
LEFT JOIN Project p
ON p.Number =ep.Project_NBR
AND p.End_DT IS NOT NULL
GROUP BY s.Skill

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 09:17:49

You're welcome

Let me know if you face any more issues.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources