Stuck on a query
-
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, approvedfrom #uslgroup by userId, SkillId, SkillLevel, TargetLevel, DateAdded, approvedResults:
rnk userId SkillId SkillLevel TargetLevel DateAdded approved-------------------- ---------- --------------- ----------- ----------- ----------------------- --------1 fred C# 4 5 2010-10-01 00:00:00.000 12 fred C# 3 5 2009-01-01 00:00:00.000 11 fred powershell 4 4 2010-10-01 00:00:00.000 12 fred powershell 3 4 2009-12-01 00:00:00.000 13 fred powershell 2 4 2009-01-01 00:00:00.000 11 fred T-SQL 5 5 2010-10-01 00:00:00.000 12 fred T-SQL 4 5 2009-09-03 00:00:00.000 13 fred T-SQL 3 5 2009-08-01 00:00:00.000 11 ginger C# 5 5 2010-10-01 00:00:00.000 12 ginger C# 4 5 2009-08-01 00:00:00.000 13 ginger C# 3 5 2008-09-03 00:00:00.000 11 ginger T-SQL 5 5 2010-10-01 00:00:00.000 02 ginger T-SQL 4 5 2009-08-01 00:00:00.000 13 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 1ginger 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_targetfrom#usljoin(select rank() over (partition by userId, skillId order by DateAdded desc) as rnk,userId, SkillId, SkillLevel, TargetLevel, DateAddedfrom #uslwhere approved = 1group by userId, SkillId, SkillLevel, TargetLevel, DateAdded) son s.userid = #usl.userid and s.skillid = #usl.skillid and s.skillLevel = #usl.skillLevelgroup by #usl.userid, #usl.approvedAny pointers would be appreciated.
I hope my requirement is clear, if not I will of course be happy to provide more info
Thanks
-
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_targetfrom#usljoin(select rank() over (partition by userId, skillId order by DateAdded desc) as rnk,userId, SkillId, SkillLevel, TargetLevel, DateAddedfrom #uslwhere approved = 1group by userId, SkillId, SkillLevel, TargetLevel, DateAdded) son s.userid = #usl.userid and s.skillid = #usl.skillid and s.skillLevel = #usl.skillLevelgroup by #usl.userid, #usl.approved -
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(SELECTuserid,skillid,MIN(CASE WHEN s.skillLevel >= s.TargetLevel AND approved = 1 THEN 0 ELSE 1 END) AS lvlFROM #usl sGROUP BY userid,skillid)SELECTuserid,1 AS approved,COUNT(*) AS num_skills ,SUM(lvl) AS skills_below_targetFROM cteGROUP BY userid -
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
-
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... -
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