July 21, 2013 at 12:21 pm
Hi all--
I have a program which brings in Skill ratings for an assessment people with the title of "Worker” have to take along with a file number they are assigned. The program also brings in the reporting line that each worker is a part of.
SELECT distinct
o.VP,
o.AVP,
o.Director,
o.Supervisor,
o.Worker,
bs.File_NBR,
s.Skill
bs.score
FROM [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;
VP AVP Director Supervisor Worker File_NBR Skill SCORE
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 are 1 through 5. What I need to do is to do is create a table that shows the count and percentage of each rating giving to the workers for each skill grouped by Vp, AVP, Supervisor and Director. So all the works that are Ultimately under a AVP and all the wokers that are Ultimately under a director and so 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 AND SO 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
I am new to SQL server and I'm have trouble figireing this out. Any help would be great, thanks!
July 21, 2013 at 10:25 pm
Can you please post the table strucutre with some sample data.....
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 22, 2013 at 4:15 pm
This copy paste is barely legible, you've got column headers on a line and then a run on sentence of information that isn't even formatted to be read. Heck one of the lines says 'oon' at the end where the alias is o and the next word is on.
SELECT distinct
o.VP,
o.AVP,
o.Director,
o.Supervisor,
o.Worker,
bs.File_NBR,
s.Skill,
bs.score
FROM 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 o
on bs.File_NBR=o.File_NBR;
So you've got some tables here. I see they join on file number and skill number. What other columns do they have? Three tables, what's the information for all three of them?
Some more data is required here. Thank you.
July 22, 2013 at 8:30 pm
Matt,
Since you're new here, I'm going to help you out and show you how to post a question so as to get a decent answer. Articles abound throughout the forum that describe this ("forum ettiquette") but here goes.
First, you need to provide some DDL. It seems in this case that you're giving us sample data that results from your stated query, but you still need to concoct a table format to put it in.
CREATE TABLE #Skills
(
VP VARCHAR(20)
,AVP VARCHAR(20)
,Director VARCHAR(20)
,Supervisor VARCHAR(20)
,Worker VARCHAR(20)
,File_NBR INT
,Skill CHAR(2)
,SCORE INT
);
Next you need to give us consumable sample data, which together with the above should run in SSMS without need to make modifications to it.
INSERT INTO #Skills
SELECT 'Gerald','Kris','Doris',NULL,'Mack',107812,'B2',4
UNION ALL SELECT 'Gerald','Kris','Doris',NULL,'Mack',107812,'D1',3
UNION ALL SELECT 'Gerald','Kris','Doris',NULL,'Mack',107812,'D2',3
UNION ALL SELECT 'Gerald','Kris','Doris',NULL,'Mack',107812,'D3',3
UNION ALL SELECT 'Gerald','Kris','Doris',NULL,'Mack',107812,'E1',4
UNION ALL SELECT 'Gerald','Kris','Mike',NULL,'Brady',109080,'A1',5
UNION ALL SELECT 'Gerald','Kris','Mike',NULL,'Brady',109080,'B1',4
UNION ALL SELECT 'Gerald','Kris','Mike',NULL,'Brady',109080,'B2',3
UNION ALL SELECT 'Gerald','Kris','Mike',NULL,'Brady',109080,'B3',4
UNION ALL SELECT 'Gerald','Kris','Mike',NULL,'Brady',109080,'C1',4
UNION ALL SELECT 'Gerald','Kris','Mike',NULL,'Brady',109080,'C2',4
UNION ALL SELECT 'Gerald','Kris','Mike',NULL,'Brady',109080,'C3',0
UNION ALL SELECT 'Kim','Harry',NULL,'Grant','Tom',108457,'B1',4
UNION ALL SELECT 'Kim','Harry',NULL,'Grant','Tom',108457,'B2',4
UNION ALL SELECT 'Kim','Harry',NULL,'Grant','Tom',108457,'C1',4
UNION ALL SELECT 'Kim','Harry',NULL,'Grant','Tom',108457,'C2',5
UNION ALL SELECT 'Kim','Harry',NULL,'Grant','Tom',108457,'C5',5
UNION ALL SELECT 'Kim','Harry',NULL,'Grant','Tom',108457,'D1',4
UNION ALL SELECT 'Kim','Harry',NULL,'Grant','Tom',108457,'D2',5
UNION ALL SELECT 'Kim','Harry',NULL,'Grant','Tom',108457,'D3',4
UNION ALL SELECT 'Kim','Harry',NULL,'Grant','Jean',106934,'C5',4
UNION ALL SELECT 'Kim','Harry',NULL,'Grant','Jean',106934,'D1',5
UNION ALL SELECT 'Kim','Harry',NULL,'Grant','Jean',106934,'D3',5
UNION ALL SELECT 'Kim','Harry',NULL,'Grant','Raphe',108901,'B2',5
UNION ALL SELECT 'Kim','Harry',NULL,'Grant','Raphe',108901,'C2',5
UNION ALL SELECT 'Kim','Harry',NULL,'Grant','Raphe',108901,'C3',4
UNION ALL SELECT 'Kim','Harry',NULL,'Grant','Raphe',108901,'C5',5
UNION ALL SELECT 'Kim','Harry',NULL,'Grant','Raphe',108901,'D2',5
UNION ALL SELECT 'Kim','Harry',NULL,'Grant','Raphe',108901,'E1',5
UNION ALL SELECT 'Kim','Harry',NULL,'Grant','Tyika',107923,'B1',5
UNION ALL SELECT 'Kim','Harry',NULL,'Grant','Tyika',107923,'B2',5
UNION ALL SELECT 'Kim','Harry',NULL,'Grant','Tyika',107923,'D2',4
UNION ALL SELECT 'Kim','Harry',NULL,'Grant','Tyika',107923,'D3',4;
Then, some nice volunteer is bound to come along and suggest a solution. The one below does an UNPIVOT using the CROSS APPLY VALUES approach (see my signature links for an article on how this works) and then uses a cross tab query to consolidate the results.
SELECT Name, [Role], b.Skill
,[0 Scores]=COUNT(CASE WHEN b.SCORE = 0 THEN 1 END)
,[0 Scores Pct Of]=100.*COUNT(CASE WHEN b.SCORE = 0 THEN 1. END)/COUNT(*)
,[1 Scores]=COUNT(CASE WHEN b.SCORE = 1 THEN 1 END)
,[1 Scores Pct Of]=100.*COUNT(CASE WHEN b.SCORE = 1 THEN 1. END)/COUNT(*)
,[2 Scores]=COUNT(CASE WHEN b.SCORE = 2 THEN 1 END)
,[2 Scores Pct Of]=100.*COUNT(CASE WHEN b.SCORE = 2 THEN 1. END)/COUNT(*)
,[3 Scores]=COUNT(CASE WHEN b.SCORE = 3 THEN 1 END)
,[3 Scores Pct Of]=100.*COUNT(CASE WHEN b.SCORE = 3 THEN 1. END)/COUNT(*)
,[4 Scores]=COUNT(CASE WHEN b.SCORE = 4 THEN 1 END)
,[4 Scores Pct Of]=100.*COUNT(CASE WHEN b.SCORE = 4 THEN 1. END)/COUNT(*)
,[5 Scores]=COUNT(CASE WHEN b.SCORE = 5 THEN 1 END)
,[5 Scores Pct Of]=100.*COUNT(CASE WHEN b.SCORE = 5 THEN 1. END)/COUNT(*)
FROM #Skills a
CROSS APPLY (
VALUES ('VP', VP, File_NBR, Skill, SCORE)
,('AVP', AVP, File_NBR, Skill, SCORE)
,('Director', Director, File_NBR, Skill, SCORE)
,('Supervisor', Supervisor, File_NBR, Skill, SCORE)
--,('Worker', Worker, File_NBR, Skill, SCORE)
) b([Role], Name, File_NBR, Skill, SCORE)
WHERE Name IS NOT NULL
GROUP BY Name, [Role], b.Skill;
GO
DROP TABLE #Skills;
Now, I am not saying this is 100% the solution to your problem but it may get you close. Please let us know if this helped you.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 22, 2013 at 8:50 pm
You are amazing! In going to try this first think when I get into the office tomorrow. I will let you know how it goes.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply