sql server - Count and Percentage of skill ratings -
i have program brings in skill ratings assessment people title of "worker” have take along file number assigned. program brings in reporting line each worker part of.
select distinct o.vp, o.avp, o.director, o.supervisor, o.worker, bs.file_nbr, s.skill bs.score [new_ees].[dbo].[sbc_best_scores] bs inner join new_ees.dbo.sbc_skills s on bs.skill_nbr=s.skill_nbr inner join gw_ppp.dbo.org_hierarchy oon bs.file_nbr=o.file_nbr; i dataset this:
vp avp director supervisor worker file_nbr skill rating gerald kris doris null mack 107812 b2 4 gerald kris doris null mack 107812 d1 3 gerald kris doris null mack 107812 d2 3 gerald kris doris null mack 107812 d3 3 gerald kris doris null mack 107812 e1 4 gerald kris mike null brady 109080 a1 5 gerald kris mike null brady 109080 b1 4 gerald kris mike null brady 109080 b2 3 gerald kris mike null brady 109080 b3 4 gerald kris mike null brady 109080 c1 4 gerald kris mike null brady 109080 c2 4 gerald kris mike null brady 109080 c3 0 kim harry null grant tom 108457 b1 4 kim harry null grant tom 108457 b2 4 kim harry null grant tom 108457 c1 4 kim harry null grant tom 108457 c2: 5 kim harry null grant tom 108457 c5 5 kim harry null grant tom 108457 d1 4 kim harry null grant tom 108457 d2 5 kim harry null grant tom 108457 d3 4 kim harry null grant jean 106934 c5 4 kim harry null grant jean 106934 d1 5 kim harry null grant jean 106934 d3 5 kim harry null grant raphe 108901 b2 5 kim harry null grant raphe 108901 c2 5 kim harry null grant raphe 108901 c3 4 kim harry null grant raphe 108901 c5 5 kim harry null grant raphe 108901 d2 5 kim harry null grant raphe 108901 e1 5 kim harry null grant tyika 107923 b1 5 kim harry null grant tyika 107923 b2 5 kim harry null grant tyika 107923 d2 4 kim harry null grant tyika 107923 d3 4 the rating levels 1 through 5. need do create table shows count , percentage of each rating giving workers each skill grouped vp, avp, supervisor , director. works under avp , wokers under director , on.
name role skill count of % of count of % of rating 1 rating 1 rating 2 rating 2 gerald vp a1 100 29% 130 33% gerald vp b1 95 28% 95 24% gerald vp b2 120 35% 70 18% gerald vp b3 30 9% 100 25% kim vp a1 kim vp b1 kim vp b2 , on kim vp b3 kris avp a1 kris avp b1 kris avp b2 kris avp b3 harry avp a1 harry avp b1 harry avp b2 harry avp b3 doris director a1 doris director b1 doris director b2 doris director b3 mike director a1 mike director b1 mike director b2 mike director b3 grant supervisor a1 grant supervisor b1 grant supervisor b2 grant supervisor b3 any assistance great! thanks!
since have different roles in different columns, compact query, either need dynamic sql or complex pivot. hence, i've opted copy , paste because don't think complexity worth 4 roles have.
i've named query t example.
with roles ( select vp name, 'vp' role, skill, rating t vp not null union select avp name, 'avp' role, skill, rating t avp not null union select director name, 'director' role, skill, rating t director not null union select supervisor name, 'supervisor' role, skill, rating t supervisor not null ), counts ( select name, role, skill ,count(case when rating = 1 1 else null end) [count of rating 1] ,count(case when rating = 2 1 else null end) [count of rating 2] ,count(case when rating = 3 1 else null end) [count of rating 3] ,count(case when rating = 4 1 else null end) [count of rating 4] ,count(case when rating = 5 1 else null end) [count of rating 5] ,count(*) totalcount roles group name, role, skill ) select name, role, skill ,[count of rating 1] ,convert(varchar(10), convert(int,100.0 * [count of rating 1]/nullif(totalcount, 0))) + '%' [% of rating 1] ,[count of rating 2] ,convert(varchar(10), convert(int,100.0 * [count of rating 2]/nullif(totalcount, 0))) + '%' [% of rating 2] ,[count of rating 3] ,convert(varchar(10), convert(int,100.0 * [count of rating 3]/nullif(totalcount, 0))) + '%' [% of rating 3] ,[count of rating 4] ,convert(varchar(10), convert(int,100.0 * [count of rating 4]/nullif(totalcount, 0))) + '%' [% of rating 4] ,[count of rating 5] ,convert(varchar(10), convert(int,100.0 * [count of rating 5]/nullif(totalcount, 0))) + '%' [% of rating 5] counts order name, skill what did here union roles together, hard coding role names. roles reorganizes table has vp gets row vp, has avp gets row avp, .... counts counts workers each name, role, , skill. final select computes percentages.
here's fiddle showing in action: http://sqlfiddle.com/#!3/fe09d/15
Comments
Post a Comment