jeudi 7 juillet 2016

MSSQL Job Alerts, find matches from user saved searches

I'm trying to create a script that checks user's 'saved searches' against live jobs.

The desired output would be a table/list of; JobID + JobSearchID.

To approach this I've created a temp table, #TempJobSavedSearches.

SELECT
    JS.JobSearchID, 
    JS.JobSortByOptionID, 
    Radius, 
    JobTypeID, 
    JobSearchKeywordID, 
    TownCityLookupID, 
    CountryID, 
    WorkingHoursID, 
    SectorID, 
    SalaryFrom, 
    SalaryTo 
INTO #TempJobSavedSearches
FROM UserJobSearches AS UJS
JOIN JobSearches AS JS ON UJS.JobSearchID = JS.JobSearchID
LEFT JOIN JobSearchJobTypes ON JS.JobSearchID = JobSearchJobTypes.JobSearchID
LEFT JOIN JobSearchStrings ON JS.JobSearchID = JobSearchStrings.JobSearchID
LEFT JOIN JobSearchTowns ON JS.JobSearchID = JobSearchTowns.JobSearchID
LEFT JOIN JobSearchWorkingHours ON JS.JobSearchID = JobSearchWorkingHours.JobSearchID
LEFT JOIN JobSearchCountries ON JS.JobSearchID = JobSearchCountries.JobSearchID
LEFT JOIN JobSearchSectors ON JS.JobSearchID = JobSearchSectors.JobSearchID
LEFT JOIN JobSearchSalaries ON JS.JobSearchID = JobSearchSalaries.JobSearchID
LEFT JOIN JobSortByOptions ON JS.JobSortByOptionID = JobSortByOptions.JobSortByOptionID
WHERE Alert = 1

This creates;

JobSearchID JobSortByOptionID Radius      JobTypeID   JobSearchKeywordID TownCityLookupID CountryID   WorkingHoursID SectorID    SalaryFrom  SalaryTo
----------- ----------------- ----------- ----------- ------------------ ---------------- ----------- -------------- ----------- ----------- -----------
901         8                 200         1           NULL               1                4           2              31          30001       40000
901         8                 200         1           NULL               1                4           2              34          30001       40000
904         8                 10          1           114                1                4           2              23          30001       40000
904         8                 10          1           114                1                4           2              24          30001       40000
904         8                 10          1           114                1                4           2              39          30001       40000

I then thought I could loop through each JobSearchID, and compare the respective data to my Jobs table.

Jobs:

JobID TownCityLookupID WorkingHoursID JobTypeID JobTitle JobDescription SalaryFrom SalaryTo 
----- ---------------- -------------- --------- -------- -------------- ---------- --------
1     1                2              5         foo      foo            10000      60000
2     1                2              1         foo      foo            30000      60000
3     11               3              3         bar      bar            70000      100000

Other link tables (some tables/columns excluded for brevity);

JobSectors

SectorID JobID
-------- -----
18       1
19       1
20       1
21       1
23       2
24       2
25       2
26       2
44       3


TownCities

TownCityLookupID CountryID
---------------- ---------
1                4
11               8

Heres my loop/attempt and comparing the results;

DECLARE @JobSearchID int = 0
DECLARE @CountryID int = 0
DECLARE @TownCityLookupID int = 0
DECLARE @WorkingHoursID int = 0
DECLARE @SalaryFrom int = 0
DECLARE @SalaryTo int = 0

WHILE(1 = 1)
BEGIN

  SELECT @JobSearchID = MIN(JobSearchID)
  FROM #TempJobSavedSearches WHERE JobSearchID > @JobSearchID

  IF @JobSearchID IS NULL BREAK

  -- store single vars
  SELECT @CountryID = (SELECT TOP(1) CountryID FROM #TempJobSavedSearches WHERE JobSearchID = @JobSearchID)
  SELECT @TownCityLookupID = (SELECT TOP(1) TownCityLookupID FROM #TempJobSavedSearches WHERE JobSearchID = @JobSearchID)
  SELECT @WorkingHoursID = (SELECT TOP(1) WorkingHoursID FROM #TempJobSavedSearches WHERE JobSearchID = @JobSearchID)
  SELECT @SalaryFrom = (SELECT TOP(1) SalaryFrom FROM #TempJobSavedSearches WHERE JobSearchID = @JobSearchID)
  SELECT @SalaryTo = (SELECT TOP(1) SalaryTo FROM #TempJobSavedSearches WHERE JobSearchID = @JobSearchID)


  -- should I be joining on jobs not on temp table?
  SELECT @JobSearchID, JobID FROM #TempJobSavedSearches
  INNER JOIN JobSectors ON #TempJobSavedSearches.SectorID = JobSectors.SectorID 
  INNER JOIN JobSortByOptions ON #TempJobSavedSearches.JobSortByOptionID = JobSortByOptions.JobSortByOptionID 
  INNER JOIN JobTypes ON #TempJobSavedSearches.JobTypeID = JobTypes.JobTypeID
  INNER JOIN JobSearchKeywords ON #TempJobSavedSearches.JobSearchKeywordID = JobSearchKeywords.JobSearchKeywordID

  WHERE 
    JobSearchID = @JobSearchID 
    AND CountryID = @CountryID 
    TownCityLookupID = @TownCityLookupID 
    AND WorkingHoursID = @WorkingHoursID
    AND SalaryFrom < @SalaryTo 
    AND SalaryTo > @SalaryFrom

END

However its returning both IDs, where I expected only JobID 2 to be shown

JobSearchID JobID
----------- -----
901         1
901         2
901         1
901         2
901         1
901         2
901         1
901         2
901         1
901         2
901         1
901         2
901         1
901         2
901         1
901         2

Could anyone explain what im doing wrong. Or provide a link to some reading that could help?

Aucun commentaire:

Enregistrer un commentaire