Rewrite my working SQL Script

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 ExecutiveSET NOCOUNT ON;DECLARE @Person intDECLARE PersonCursor CURSORFOR SELECT  ExFcode FROM Executive ORDER BY ExFcode ASC;OPEN PersonCursor;FETCH NEXT FROM PersonCursorINTO @Person;WHILE @@FETCH_STATUS = 0BEGINFETCH NEXT FROM PersonCursorINTO @Person–Get the myFields data for the person being analyzedSELECT DISTINCT Executive.ExFcode, JobFunction.Code, Company.coFcode, ExecutiveJobs.IsCurrent, ExecutiveJobs.isBoardMember, ExecutiveJobs.IsPrimary,Company.industry, Company.sic, ExecutiveEducation.CollegeName,, Company.state INTO #myFieldsFROM Executive LEFT JOIN ExecutiveJobs ON ExecutiveJobs.ExFcode=Executive.ExFcodeLEFT JOIN ExecutiveJobFunction ON ExecutiveJobFunction.ExFcode=Executive.ExFcodeLEFT JOIN JobFunction ON JobFunction.Code=ExecutiveJobFunction.JobFunctionLEFT JOIN CompanyON Company.coFcode=ExecutiveJobs.CoFcodeLEFT JOIN ExecutiveEducationON ExecutiveEducation.ExFcode=Executive.ExFcodeWHERE Executive.ExFcode=@Person–Put individual Fields into temp tablesSELECT DISTINCT Code INTO #myJobs FROM #myFields –all jobsSELECT DISTINCT coFcode INTO #myFcode FROM #myFieldsSELECT DISTINCT industry INTO #myindustry FROM #myFieldsSELECT DISTINCT sic INTO #mysic FROM #myFieldsSELECT DISTINCT CollegeName INTO #myCollege FROM #myFieldsSELECT DISTINCT City INTO #myCity FROM #myFieldsSELECT DISTINCT coFcode INTO #myBoards FROM #myFields WHERE isBoardMember=1 –Temporary results tableCREATE 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 CompanyINSERT INTO #Results (ExFcode, CurrCo)SELECT DISTINCT ExFcode, COUNT(DISTINCT CoFcode) AS CurrCoFROM ExecutiveJobsWHERE ExecutiveJobs.CoFcode IN (SELECT * FROM #myFcode)AND ExecutiveJobs.IsCurrent=1GROUP BY ExFcodeORDER BY ExFcode–All job titles past and presentINSERT INTO #Results (ExFcode, Titles)SELECT DISTINCT ExecutiveJobs.ExFcode, COUNT(DISTINCT JobFunction) AS TitlesFROM ExecutiveJobFunctionINNER JOIN ExecutiveJobs ON ExecutiveJobs.ExFcode=ExecutiveJobFunction.ExFcodeWHERE ExecutiveJobFunction.JobFunction IN (SELECT * FROM #myJobs)–AND ExecutiveJobs.IsPrimary=1–AND ExecutiveJobs.IsCurrent=1GROUP BY ExecutiveJobs.ExFcodeORDER BY ExecutiveJobs.ExFcode–Past CompaniesINSERT INTO #Results (ExFcode, PastCo)SELECT DISTINCT ExFcode, COUNT(DISTINCT CoFcode) AS PastCoFROM ExecutiveJobsWHERE ExecutiveJobs.CoFcode IN (SELECT * FROM #myFcode)AND ExecutiveJobs.IsCurrent=0GROUP BY ExFcodeORDER BY ExFcode–All industriesINSERT INTO #Results (ExFcode, Indust)SELECT DISTINCT ExecutiveJobs.ExFcode, COUNT(DISTINCT Company.industry) AS IndustFROM ExecutiveJobsINNER JOIN CompanyON Company.CoFcode=ExecutiveJobs.CoFcodeWHERE Company.industry IN (SELECT * FROM #myindustry)GROUP BY ExFcodeORDER BY ExFcode–All SubIndustriesINSERT INTO #Results (ExFcode, sic)SELECT DISTINCT ExecutiveJobs.ExFcode, COUNT(DISTINCT Company.industry) AS sicFROM ExecutiveJobsINNER JOIN CompanyON Company.CoFcode=ExecutiveJobs.CoFcodeWHERE Company.sic IN (SELECT * FROM #mysic)GROUP BY ExFcodeORDER BY ExFcode–Everyone from All Colleges attended by personINSERT INTO #Results (ExFcode, College)SELECT DISTINCT ExecutiveEducation.ExFcode, COUNT(DISTINCT ExecutiveEducation.CollegeName) AS CollegeFROM ExecutiveEducationWHERE ExecutiveEducation.CollegeName IN (SELECT * FROM #myCollege)GROUP BY ExFcodeORDER BY ExFcode–Everyone in the City of primary company of personINSERT INTO #Results (ExFcode, City)SELECT DISTINCT ExecutiveJobs.ExFcode, COUNT(DISTINCT AS cityFROM ExecutiveJobsINNER JOIN CompanyON Company.CoFcode=ExecutiveJobs.CoFcodeWHERE IN (SELECT * FROM #myCity)GROUP BY ExFcodeORDER BY ExFcode–Board MembersINSERT INTO #Results (ExFcode, Board)SELECT DISTINCT ExecutiveJobs.ExFcode, COUNT(DISTINCT CoFcode) as BoardFROM ExecutiveJobsWHERE CoFcode IN (SELECT * FROM #myBoards)GROUP BY ExFcodeORDER BY ExFcode–Consolidate the results into permanent Connections Table, Create ScoreINSERT INTO ConnectionsSELECT TOP 20 ExFcode As Connections, @Person As Executive, Sum(CurrCo) As SameCompany, –The TOP N connections onlySum(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)>3THEN 3ELSE Sum(isnull(Titles,0))END)As Score, GETDATE() As Date–INTO ConnectionsFROM #ResultsGROUP BY ExFcodeORDER BY Score DESC–Clean Up temp tablesDROP TABLE #myFieldsDROP TABLE #myJobsDROP TABLE #myFcodeDROP TABLE #myindustryDROP TABLE #mysicDROP TABLE #myCollegeDROP TABLE #myCityDROP TABLE #myBoardsDROP TABLE #Results –DROP TABLE Connections;END –FETCH_STATUS LOOPCLOSE PersonCursor;DEALLOCATE PersonCursor

Looking for this or a Similar Assignment? Click below to Place your Order

Open chat
%d bloggers like this: