Rewrite my working SQL Script
User Generated
pbeznpxvaarl
Programming
Description
I'm a very weak SQL programmer, but this script does work properly. Problem is that it is very slow, and a pig. Please rewrite it to produce the same result, and work much more efficiently.
/**This batch script compares every person's profile to each other person in the database.
The matches are tallied (ie # job titles, # past companies, # schools) and using weights,
a total Score is calculated. The top matches are recorded - the individual's top match is himself**/
SELECT COUNT(ExFcode) FROM Executive
SET NOCOUNT ON;
DECLARE @Person int
DECLARE PersonCursor CURSOR
FOR SELECT ExFcode FROM Executive ORDER BY ExFcode ASC;
OPEN PersonCursor;
FETCH NEXT FROM PersonCursor
INTO @Person;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM PersonCursor
INTO @Person
--Get the myFields data for the person being analyzed
SELECT DISTINCT Executive.ExFcode, JobFunction.Code, Company.coFcode,
ExecutiveJobs.IsCurrent, ExecutiveJobs.isBoardMember, ExecutiveJobs.IsPrimary,
Company.industry, Company.sic, ExecutiveEducation.CollegeName,
Company.city, Company.state INTO #myFields
FROM Executive
LEFT JOIN ExecutiveJobs
ON ExecutiveJobs.ExFcode=Executive.ExFcode
LEFT JOIN ExecutiveJobFunction
ON ExecutiveJobFunction.ExFcode=Executive.ExFcode
LEFT JOIN JobFunction
ON JobFunction.Code=ExecutiveJobFunction.JobFunction
LEFT JOIN Company
ON Company.coFcode=ExecutiveJobs.CoFcode
LEFT JOIN ExecutiveEducation
ON ExecutiveEducation.ExFcode=Executive.ExFcode
WHERE Executive.ExFcode=@Person
--Put individual Fields into temp tables
SELECT DISTINCT Code INTO #myJobs FROM #myFields --all jobs
SELECT DISTINCT coFcode INTO #myFcode FROM #myFields
SELECT DISTINCT industry INTO #myindustry FROM #myFields
SELECT DISTINCT sic INTO #mysic FROM #myFields
SELECT DISTINCT CollegeName INTO #myCollege FROM #myFields
SELECT DISTINCT City INTO #myCity FROM #myFields
SELECT DISTINCT coFcode INTO #myBoards FROM #myFields WHERE isBoardMember=1
--Temporary results table
CREATE TABLE #Results (ExFcode int, CurrCo int, Titles int, PastCo int,
Indust int, sic int, College int, City int, Board int, Score int)
--Populate results (matches) for each field among myFields
--Current Company
INSERT INTO #Results (ExFcode, CurrCo)
SELECT DISTINCT ExFcode, COUNT(DISTINCT CoFcode) AS CurrCo
FROM ExecutiveJobs
WHERE ExecutiveJobs.CoFcode IN (SELECT * FROM #myFcode)
AND ExecutiveJobs.IsCurrent=1
GROUP BY ExFcode
ORDER BY ExFcode
--All job titles past and present
INSERT INTO #Results (ExFcode, Titles)
SELECT DISTINCT ExecutiveJobs.ExFcode, COUNT(DISTINCT JobFunction) AS Titles
FROM ExecutiveJobFunction
INNER JOIN ExecutiveJobs
ON ExecutiveJobs.ExFcode=ExecutiveJobFunction.ExFcode
WHERE ExecutiveJobFunction.JobFunction IN (SELECT * FROM #myJobs)
--AND ExecutiveJobs.IsPrimary=1
--AND ExecutiveJobs.IsCurrent=1
GROUP BY ExecutiveJobs.ExFcode
ORDER BY ExecutiveJobs.ExFcode
--Past Companies
INSERT INTO #Results (ExFcode, PastCo)
SELECT DISTINCT ExFcode, COUNT(DISTINCT CoFcode) AS PastCo
FROM ExecutiveJobs
WHERE ExecutiveJobs.CoFcode IN (SELECT * FROM #myFcode)
AND ExecutiveJobs.IsCurrent=0
GROUP BY ExFcode
ORDER BY ExFcode
--All industries
INSERT INTO #Results (ExFcode, Indust)
SELECT DISTINCT ExecutiveJobs.ExFcode, COUNT(DISTINCT Company.industry) AS Indust
FROM ExecutiveJobs
INNER JOIN Company
ON Company.CoFcode=ExecutiveJobs.CoFcode
WHERE Company.industry IN (SELECT * FROM #myindustry)
GROUP BY ExFcode
ORDER BY ExFcode
--All SubIndustries
INSERT INTO #Results (ExFcode, sic)
SELECT DISTINCT ExecutiveJobs.ExFcode, COUNT(DISTINCT Company.industry) AS sic
FROM ExecutiveJobs
INNER JOIN Company
ON Company.CoFcode=ExecutiveJobs.CoFcode
WHERE Company.sic IN (SELECT * FROM #mysic)
GROUP BY ExFcode
ORDER BY ExFcode
--Everyone from All Colleges attended by person
INSERT INTO #Results (ExFcode, College)
SELECT DISTINCT ExecutiveEducation.ExFcode, COUNT(DISTINCT ExecutiveEducation.CollegeName) AS College
FROM ExecutiveEducation
WHERE ExecutiveEducation.CollegeName IN (SELECT * FROM #myCollege)
GROUP BY ExFcode
ORDER BY ExFcode
--Everyone in the City of primary company of person
INSERT INTO #Results (ExFcode, City)
SELECT DISTINCT ExecutiveJobs.ExFcode, COUNT(DISTINCT Company.city) AS city
FROM ExecutiveJobs
INNER JOIN Company
ON Company.CoFcode=ExecutiveJobs.CoFcode
WHERE Company.city IN (SELECT * FROM #myCity)
GROUP BY ExFcode
ORDER BY ExFcode
--Board Members
INSERT INTO #Results (ExFcode, Board)
SELECT DISTINCT ExecutiveJobs.ExFcode, COUNT(DISTINCT CoFcode) as Board
FROM ExecutiveJobs
WHERE CoFcode IN (SELECT * FROM #myBoards)
GROUP BY ExFcode
ORDER BY ExFcode
--Consolidate the results into permanent Connections Table, Create Score
INSERT INTO Connections
SELECT TOP 20 ExFcode As Connections, @Person As Executive, Sum(CurrCo) As SameCompany, --The TOP N connections only
Sum(Titles) AS SameTitle,
SUM(Indust) As SameIndustry, SUM(sic) As SameSubInd,
SUM(College) AS SameCollege, SUM(City) AS SameCity, SUM(Board) As SameBoard,
--HERE IS THE SCORE WEIGHT (and Limit for Title matches)
(5*Sum(isnull(CurrCo,0)) +
SUM(isnull(Indust,0)) +
2*SUM(isnull(sic,0)) +
3*SUM(isnull(College,0)) +
2*SUM(isnull(City,0)) +
10*SUM(isnull(Board,0)) +
Case
WHEN SUM(Titles)>3
THEN 3
ELSE Sum(isnull(Titles,0))
END)
As Score, GETDATE() As Date
--INTO Connections
FROM #Results
GROUP BY ExFcode
ORDER BY Score DESC
--Clean Up temp tables
DROP TABLE #myFields
DROP TABLE #myJobs
DROP TABLE #myFcode
DROP TABLE #myindustry
DROP TABLE #mysic
DROP TABLE #myCollege
DROP TABLE #myCity
DROP TABLE #myBoards
DROP TABLE #Results
--DROP TABLE Connections;
END --FETCH_STATUS LOOP
CLOSE PersonCursor;
DEALLOCATE PersonCursor
This question has not been answered.
Create a free account to get help with this and any other question!
24/7 Homework Help
Stuck on a homework question? Our verified tutors can answer all questions, from basic math to advanced rocket science!
Similar Content
Related Tags
Pride and Prejudice
by Jane Austen
The Two Towers
by J. R. R. Tolkien
Freakonomics
by Stephen J. Dubner and Steven D. Levitt
The Power of Habit - Why We Do What We Do in Life and Business
by Charles Duhigg
Team of Vipers
by Cliff Sims
The English Patient
by Michael Ondaatje
How to Win at the Sport of Business
by Mark Cuban
The Road
by Cormac McCarthy
The Joy Luck Club
by Amy Tan