October 22, 2010 at 9:09 am
Hi folks
I'm either having a touch of the friday afternoon stupids, or I'm a lot rustier than I thought.
In a nutshell, this is a (very) simplified version of an employee appraisal system. Employees have a number of skills, a current skill level for each skill, along with a target level for each skill. Prior to having an appraisal, the employee will enter the skill level they believe they are at, and this has to be approved by a manager.
I want to return, for each employee, the number of skills they have, and also the number of "skills below target level"
Here's the DDL and sample data
create table #usl (
userId varchar(10),
skillId varchar(15),
skillLevel int,
targetLevel int,
approved bit,
DateAdded datetime
)
insert into #usl select 'fred', 'T-SQL', 4, 5, 1, '2009-09-03'
insert into #usl select 'fred', 'T-SQL', 3, 5, 1, '2009-08-01'
insert into #usl select 'fred', 'T-SQL', 5, 5, 1, '2010-10-01'
insert into #usl select 'fred', 'C#', 4, 5, 1, '2010-10-01'
insert into #usl select 'fred', 'C#', 3, 5, 1, '2009-01-01'
insert into #usl select 'fred', 'powershell', 2, 4, 1, '2009-01-01'
insert into #usl select 'fred', 'powershell', 3, 4, 1, '2009-12-01'
insert into #usl select 'fred', 'powershell', 4, 4, 1, '2010-10-01'
insert into #usl select 'ginger', 'T-SQL', 3, 5, 1, '2008-09-03'
insert into #usl select 'ginger', 'T-SQL', 4, 5, 1, '2009-08-01'
insert into #usl select 'ginger', 'T-SQL', 5, 5, 0, '2010-10-01'
insert into #usl select 'ginger', 'C#', 3, 5, 1, '2008-09-03'
insert into #usl select 'ginger', 'C#', 4, 5, 1, '2009-08-01'
insert into #usl select 'ginger', 'C#', 5, 5, 1, '2010-10-01'
This query returns the result set below
select rank() over (partition by userId, skillId order by DateAdded desc) as rnk,
userId, SkillId, SkillLevel, TargetLevel, DateAdded, approved
from #usl
group by userId, SkillId, SkillLevel, TargetLevel, DateAdded, approved
Results:
rnk userId SkillId SkillLevel TargetLevel DateAdded approved
-------------------- ---------- --------------- ----------- ----------- ----------------------- --------
1 fred C# 4 5 2010-10-01 00:00:00.000 1
2 fred C# 3 5 2009-01-01 00:00:00.000 1
1 fred powershell 4 4 2010-10-01 00:00:00.000 1
2 fred powershell 3 4 2009-12-01 00:00:00.000 1
3 fred powershell 2 4 2009-01-01 00:00:00.000 1
1 fred T-SQL 5 5 2010-10-01 00:00:00.000 1
2 fred T-SQL 4 5 2009-09-03 00:00:00.000 1
3 fred T-SQL 3 5 2009-08-01 00:00:00.000 1
1 ginger C# 5 5 2010-10-01 00:00:00.000 1
2 ginger C# 4 5 2009-08-01 00:00:00.000 1
3 ginger C# 3 5 2008-09-03 00:00:00.000 1
1 ginger T-SQL 5 5 2010-10-01 00:00:00.000 0
2 ginger T-SQL 4 5 2009-08-01 00:00:00.000 1
3 ginger T-SQL 3 5 2008-09-03 00:00:00.000 1
(14 row(s) affected)
In fred's case, he has only one "skills below target level", in the C# skill.
In ginger's case, she also has one skill below target level, in T-SQL, as the 2010-10-01 record has not been approved.
I would like to get the result below:
userid approved num_skills skills_below_target
---------- -------- ----------- -------------------
fred 1 3 1
ginger 1 2 1
(2 row(s) affected)
Here is the query I've got so far, but it's wrong and I am stuck:
select #usl.userid, #usl.approved, count(distinct #usl.skillId) as num_skills,
count(case when s.rnk = 1 and s.skillLevel < s.TargetLevel then 1 else 0 end)
as skills_below_target
from
#usl
join
(select rank() over (partition by userId, skillId order by DateAdded desc) as rnk,
userId, SkillId, SkillLevel, TargetLevel, DateAdded
from #usl
where approved = 1
group by userId, SkillId, SkillLevel, TargetLevel, DateAdded) s
on s.userid = #usl.userid and s.skillid = #usl.skillid and s.skillLevel = #usl.skillLevel
group by #usl.userid, #usl.approved
Any pointers would be appreciated.
I hope my requirement is clear, if not I will of course be happy to provide more info
Thanks
October 22, 2010 at 9:18 am
Ah - I've got it (needed SUM instead of COUNT). Doh!
select #usl.userid, #usl.approved, count(distinct #usl.skillId) as num_skills,
sum(case when s.rnk = 1 and s.skillLevel < s.TargetLevel then 1 else 0 end)
as skills_below_target
from
#usl
join
(select rank() over (partition by userId, skillId order by DateAdded desc) as rnk,
userId, SkillId, SkillLevel, TargetLevel, DateAdded
from #usl
where approved = 1
group by userId, SkillId, SkillLevel, TargetLevel, DateAdded) s
on s.userid = #usl.userid and s.skillid = #usl.skillid and s.skillLevel = #usl.skillLevel
group by #usl.userid, #usl.approved
October 22, 2010 at 3:11 pm
Ok, here's a slightly different approach.
When running your second sample, I didn't get yuor expected result. Is that intentional?
If not, here's a query that will return your expected result and should perform better, too:
;
WITH cte AS
(
SELECT
userid,
skillid,
MIN(CASE WHEN s.skillLevel >= s.TargetLevel AND approved = 1 THEN 0 ELSE 1 END) AS lvl
FROM #usl s
GROUP BY userid,skillid
)
SELECT
userid,
1 AS approved,
COUNT(*) AS num_skills ,
SUM(lvl) AS skills_below_target
FROM cte
GROUP BY userid
October 25, 2010 at 3:37 am
LutzM (10/22/2010)
Ok, here's a slightly different approach.When running your second sample, I didn't get yuor expected result. Is that intentional?
Odd - it returns the expected result for me. I've triple checked too (Friday afternoons and Monday mornings aren't my best time...)
If not, here's a query that will return your expected result and should perform better, too:
*snip code*
Thanks Lutz, I never thought of using a CTE. I'll have a go at incorporating it into the behemoth query that this is a simplified version of!
Thanks again
October 25, 2010 at 10:39 am
Hmmm...
I've re-run your code and get the same results now.
Must have been a problem with my system back then - either the part behind the screen or in front if it (probably the latter... 😉 . Sorry for the confusion...
October 25, 2010 at 10:45 am
LutzM (10/25/2010)
Hmmm...I've re-run your code and get the same results now.
Must have been a problem with my system back then - either the part behind the screen or in front if it (probably the latter... 😉 . Sorry for the confusion...
Heh, I've been there more times than I'd like to think... Thanks again for the help 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply