View 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)

View Problem

Author

Topic

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-11-21 : 01:47:00

Hi i have Tables - Employee_skill ,Skill_level, Skill, Skill Category, Employee

Employee_skill

Skill_NBR Employee_nbr self_Rating supervisor_rating Date Supervisor_Approved_Flag
1 1 3 3 01/01/2007 1
2 1 4 2 08/01/2007 1
3 5 2 2 02/10/2007 1
2 2 4 3 02/10/2007 1
5 2 3 4 02/10/2007 1
5 3 4 4 25/10/2008 1
5 1 5 4 25/10/2008 1
7 1 3 3 25/10/2008 1
5 3 5 null null 0
2 2 4 4 11/19/2008 1

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

Skill
Number Name Category_Number
1 .Net 1
2 SQL server 2
3 Java 1
4 Manual Testing 1
12 Siebel 1
5 oracle 2
6 XML 1
7 sybase 2

Skill Category
Number Name
1 Programming
2 Database

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

I need a View Which Will display the Employee Skills With Rating(Supervisor Rating). If the Supervisor Rating is null then Self Rating should be displayed. I need the Columns Employee nbr, Skill name, skill Category Name in the View. If the same Skill is approved twice then the only the latest approved record should be displayed. If the Employee has again self rated again for the Skill which is already approved (Supervisor_Rating, date will be null and Supervisor_Approved_Flag value will be zero in Employee Skills Table) Then the old record should not be displayed and only new record with Employee self rating to be displayed.

Thanks in Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 02:05:09

you've asking similar questions over same topic for some time now. Can i see what you tried so far. We've provided you enough & more solutions till date so this should be a simple tweak from your previously provided solutions. try it out yourself before posting it.If you face any problems ask for help. Dont expect solutions to be spoonfeeded to you everytime

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113773
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113695

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-11-21 : 02:28:18

Sorry Vishak.. i've actually tried it. But dint get.SO posted Here.
Well as u said i would also post the code which i tried
This is the Code Which i Wrote

Create View VW_EX
AS
BEGIN
SELECT DISTINCT es.Number
,ISNULL(sc.NumBeR,sc1.NumBeR)
,IsNULL(sc.NAme,sc1.NAme)
,ISNULL(s.Number,s1.Number)
,ISNULL(s.Name,s1.Name)
,ISNULL(sl.Number,es.Self_Rating) AS Skill_Level_NBR

FROM Employee_Skills es
INNER JOIN Skill s
ON s.Number=es.Skill_NBR
INNER JOIN TSkill_Category sc
ON s.Category_Number = sc.NumBeR
INNER JOIN Skill_Level sl
ON es.Supervisor_Rating = sl.Number
INNER JOIN Skill_Level sl1
ON es.Skill_Level_NBR=sl1.Number
INNER JOIN Skill s1 ON
es.Skill_NBR=s1.Number
INNER JOIN Skill_Category sc1
ON s1.Category_Number=sc1.Number
WHERE es.Supervisor_Approved_FlaG = 1 OR es.Supervisor_Approved_FlaG=0
ORDER BY es.Date
END

When i Open the View it Just Displays only Approved Rating Details.
But i dint understand how to incorporate the conditions which i've mentioned

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

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

[code]Create View VW_EX
AS
BEGIN
SELECT EmpNo,SkillName,SkillCategoryName,Rating
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Employee_nbr ORDER BY COALESCE(Date,'1900-01-01') DESC) AS Seq,
es.Employee_nbr AS EmpNo,
s.Name as SkillName,
sc.Name as SkillCategoryName,
COALESCE(es.supervisor_rating,es.self_rating) AS Rating
SELECT
FROM Employee_Skill es
INNER JOIN Skill s
ON s.Number=es.Skill_NBR
INNER JOIN SkillCategory sc
ON sc.Number=s.Category_Number
)t
WHERE t.Seq=1
END [/code]

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-11-21 : 04:56:08

Thanks Vishakh for the Solution

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 05:11:18

you're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources