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


User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

This question has not been answered.

Create a free account to get help with this and any other question!

Related Tags