Faire de la logique avancée en SQL (T-SQL) – Algorithme de correspondance

Ok, donc ce cas d'utilisation particulier me donne pas mal de mal de tête.

Ceci est une édition d'un autre article, où j'ai oublié d'append un aspect important à l'usecase.

Ce qu'il faut faire est de faire correspondre un Candidat WorkWish avec le CustomerJobQueries dans la database.

Cela signifie que la correspondance entre DateTimes et WorkWish se chevauche avec l'une des CustomerJobQueries . Ensuite, je dois faire correspondre le GeographicalPreference avec l'location du client.

La logique que nous avions quand nous construisons la database était la suivante:

si le pays est défini mais que la région et la commune sont NULL, le candidat souhaite travailler dans tout le pays.
Si le pays et la région sont définis, le candidat souhaite travailler dans toutes les municipalités de la région spécifique du pays concerné.
Si le pays, la région et la municipalité sont établis, il souhaite ne travailler que dans cette municipalité.

Il est possible que plusieurs lignes existent, de telle sorte que 2 municipalités spécifiques soient définies, la seule règle est la suivante: il n'y aura jamais de ligne ayant la priorité sur une autre.

sens

GeographicalPreferenceId Country_ShortName Region_Id Municipality_Id WorkWish_Id ------------------------ -------------------- ----------- --------------- ----------- 1 DK NULL NULL 1 2 DK 3 NULL 1

N'est PAS possible car une rangée précise déjà qu'il souhaite travailler dans tout le pays.

Voici comment un extrait de la database ressemble

Table DateRange – contient toutes les plages de dates

 DateRangeId StartDate EndDate CustomerJobQuery_Id WorkWish_Id ----------- --------------------------- --------------------------- ------------------- ----------- 1 2014-03-31 00:00:00.0000000 2014-08-18 00:00:00.0000000 NULL 1 2 2014-08-25 00:00:00.0000000 2014-09-15 00:00:00.0000000 NULL 1 3 2013-08-24 00:00:00.0000000 2014-09-25 00:00:00.0000000 1 NULL

Table Workwish

 Id Candidate_Id ----------- ------------ 1 5

Table CustomerJobQuery

 Id Customer_CustomerId Country_ShortName Region_Id Municipality_Id ----------- ------------------- ----------------- --------- ----------------- 1 2 DK 2 3

Table de reference géographique

 GeographicalPreferenceId Country_ShortName Region_Id Municipality_Id WorkWish_Id ------------------------ -------------------- ----------- --------------- ----------- 1 DK 2 NULL 1 2 DK 3 NULL 1

Ce qui est jusqu'ici est; – Renvoie les CustomerJobQueries qui chevauchent les dates WorkWish.

 SELECT c.* FROM CustomerJobQuery c WHERE EXISTS ( SELECT * FROM Workwish w INNER JOIN DateRange d1 ON d1.CustomerJobQuery_Id = c.Id INNER JOIN DateRange d2 ON d2.WorkWish_Id = w.Id WHERE w.Candidate_Id = @CandidateId -- input from my **StoredProcedure** -- overlap check: AND d1.StartDate <= d2.EndDate AND d2.StartDate <= d1.EndDate

)

Je ne sais pas comment je ferais la logique de preference géographique de manière fixe. – Actuellement notre solution est faite en C # en chargeant tout en memory et en bouclant tout avec des foreaches nestedes ce qui est horriblement inefficace. Et est le pire scénario absolu.

Toute aide est très appréciée!

J'ai créé des tables, puisque vous n'avez fourni aucun DDL.

 declare @CandidateId int; declare @WorkWish table ( Id int primary key, CandidateId int ); declare @Preference table ( Id int primary key, WishId int, Country char(2), RegionId int null, MunicipalityId int null ); declare @DateRange table ( Id int primary key, StartDate datetime, EndDate datetime, JobQueryId int null, WishId int null ); declare @JobQuery table ( Id int primary key, CustomerId int, Country char(2), RegionId int, MunicipalityId int ); select *, case when p.MunicipalityId is not null then 0 when p.RegionId is not null then 1 else 2 end as [LocationMesortingc] from @WorkWish w inner join @Preference p on w.Id = p.WishId inner join @DateRange dw on dw.WishId = w.Id inner join @JobQuery j on j.Country = p.Country inner join @DateRange dj on dj.JobQueryId = j.Id where w.CandidateId = @CandidateId and dw.StartDate <= dj.EndDate and dj.StartDate <= dw.EndDate and nullif(p.RegionId, j.RegionId) is null and nullif(p.MunicipalityId, j.MunicipalityId) is null order by LocationMesortingc;

Je dois vous avertir de la performance de la fonction NULLIF() , cependant, c'est loin d'être parfait. Vous pouvez essayer de l'étendre dans une construction de case correspondante, mais cela ne vaut généralement pas la peine.

Pourtant, cela devrait être mieux que ce que vous avez maintenant.

Je pense que ce qui suit vous obtiendra tous les matchs:

 WITH ww as ( SELECT ww.*, drw.StartDate, drw.EndDate FROM WorkWish ww JOIN DateRange drw ON drw.WorkWish_Id = w.id ) SELECT c.* FROM CustomerJobQuery c JOIN DateRange drc ON drc.CustomerJobQuery_Id = c.id JOIN ww ON drc.StartDate <= ww.EndDate AND ww.StartDate <= drw.EndDate AND (drc.Country_ShortName = ww.Country_ShortName and ww.Region_id is NULL or drc.Country_ShortName = ww.Country_ShortName and drc.Region_id = ww.Region_id and ww.municipality_id is null or drc.Country_ShortName = ww.Country_ShortName and drc.Region_id = ww.Region_id and drc.Municipality_Id = ww.Munipality_Id ) )

Si vous voulez prioriser les correspondances, utilisez row_number() et une sous-requête:

 WITH ww as ( SELECT ww.*, drw.StartDate, drw.EndDate FROM WorkWish ww JOIN DateRange drw ON drw.WorkWish_Id = w.id ) SELECT c.* FROM (SELECT c.*, ww.id as WorkId, ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY (case when ww.Munipality_Id is not null then 1 when ww.Region_id is not null then 2 else 3 end) ) as seqnum FROM CustomerJobQuery c JOIN DateRange drc ON drc.CustomerJobQuery_Id = c.id JOIN ww ON drc.StartDate <= ww.EndDate AND ww.StartDate <= drw.EndDate AND (drc.Country_ShortName = ww.Country_ShortName and ww.Region_id is NULL or drc.Country_ShortName = ww.Country_ShortName and drc.Region_id = ww.Region_id and ww.municipality_id is null or drc.Country_ShortName = ww.Country_ShortName and drc.Region_id = ww.Region_id and drc.Municipality_Id = ww.Munipality_Id ) ) c WHERE seqnum = 1;