Mise à jour de la requête avec la date et l'heure les plus proches grâce à la jointure interne

Question

J'ai les deux tableaux suivants

CREATE TABLE Ep
    ([E] varchar(9), [M] varchar(9), [DTE] DATETIME)
;

INSERT INTO Ep
    ([E], [M], [DTE])
VALUES
    ('1595861-1', '1595861-1', CONVERT(datetime, '2002-11-26 14:18:00', 20)),
    ('1595904-1', '1595904-1', CONVERT(datetime, '2002-11-24 15:15:00', 20)),
    ('1596298-1', '1596298-1', CONVERT(datetime, '2002-12-17 11:12:00', 20)),
    ('1596357-1', '1596357-1', CONVERT(datetime, '2002-12-09 19:57:00', 20)),
    ('1596369-1', '1596369-1', CONVERT(datetime, '2002-12-11 06:00:00', 20)),
    ('1596370-1', '1596370-1', CONVERT(datetime, '2002-12-19 12:31:00', 20)),
    ('1596473-2', '1596473-1', CONVERT(datetime, '2002-12-15 08:39:00', 20)),
    ('1596473-3', '1596473-1', CONVERT(datetime, '2002-12-20 08:39:00', 20)),
    ('1596473-4', '1596473-1', CONVERT(datetime, '2002-12-13 08:39:00', 20)),
    ('1596473-5', '1596473-1', CONVERT(datetime, '2002-12-16 08:39:00', 20)),
    ('1596473-1', '1596473-1', CONVERT(datetime, '2002-12-14 08:39:00', 20))
;

CREATE TABLE Mp
    ([E] varchar(9), [M] varchar(9), [DTE] DATETIME)
;

INSERT INTO Mp
    ([E], [M], [DTE])
VALUES
    ('', '1595861-1', CONVERT(datetime, '2002-11-26 14:18:00', 20)),
    ('', '1595904-1', CONVERT(datetime, '2002-11-24 15:15:00', 20)),
    ('', '1596298-1', CONVERT(datetime, '2002-12-17 11:12:00', 20)),
    ('', '1596357-1', CONVERT(datetime, '2002-12-09 19:57:00', 20)),
    ('', '1596369-1', CONVERT(datetime, '2002-12-11 06:00:00', 20)),
    ('', '1596370-1', CONVERT(datetime, '2002-12-19 12:31:00', 20)),
    ('', '1596473-1', CONVERT(datetime, '2002-12-17 08:39:00', 20))
;

Je suis actuellement en train de mettre à jour le [E] dans le champ Mp par le biais d'une correspondance sur [M] où les DTE (en Mp ) se situe dans une certaine fourchette (disons +-3 jours). La requête pour ce faire est actuellement

UPDATE [Mp] 
SET [E] = [Ep].[E] 
FROM [Mp] INNER JOIN [Ep] 
    ON [Mp].[M] = [Ep].[M] 
WHERE [Mp].[DTE] BETWEEN [Ep].[DTE] - 3 AND [Ep].[DTE] + 3;

Cette mise à jour [Mp].[E] para [Mp].[M] = N'1596473-1' a 1596473-2 . Il s'agit essentiellement de la première entrée valide trouvée par SQL Server. Cependant, je veux mettre à jour cette requête pour que SQL Server fasse correspondre l'entrée à l'entrée [M] dans la plage de dates requise (comme c'est le cas actuellement), mais pour le champ [Ep].[DTE] qui se rapproche le plus de celle de la [Mp].[DTE] valeur de 2002-12-17 08:39:00 .

J'ai envisagé d'ajouter un DATEDIFF de la manière suivante

UPDATE [Mp] 
SET [E] = [Ep].[E] 
FROM [Mp] INNER JOIN [Ep] 
    ON [Mp].[M] = [Ep].[M] 
WHERE [Mp].[DTE] BETWEEN [Ep].[DTE] - 3 AND [Ep].[DTE] + 3 
ORDER BY DATEDIFF(minutes, [Mp].[DTE], [Ep].[DTE]);

Il est clair que je ne peux pas faire cela, mais je ne sais pas comment modifier cela pour que cela fonctionne. Les données finales pour [Mp] après la mise à jour devraient être

  1595861-1     1595861-1   2002-11-26 14:18:00.000 
  1595904-1     1595904-1   2002-11-24 15:15:00.000 
  1596298-1     1596298-1   2002-12-17 11:12:00.000 
  1596357-1     1596357-1   2002-12-09 19:57:00.000 
  1596369-1     1596369-1   2002-12-11 06:00:00.000 
  1596370-1     1596370-1   2002-12-19 12:31:00.000 
**1596473-5**   1596473-1   2002-12-17 08:39:00.000 

Merci pour votre temps.

Answer 1

Veuillez d'abord vérifier quelles données le CTE suivant produit en sortie

Pour les données les plus proches, j'ai utilisé Fonction SQL ROW_NUMBER avec la clause Partition By en fonction du calcul de la différence de temps récupéré par la fonction DATEDIFF(). Pour éliminer les enregistrements précédents et suivants, j'ai utilisé la fonction mathématique ABS().

select
*,
ROW_NUMBER() over (partition by [Mp].[M] order by abs(datediff(mi, [Mp].[DTE], [Ep].[DTE]))) as rn,
abs(datediff(mi, [Mp].[DTE], [Ep].[DTE])) diff
from Mp
left join Ep 
    on [Mp].[M] = [Ep].[M] 
WHERE [Mp].[DTE] BETWEEN dateadd(dd,-3,[Ep].[DTE]) AND dateadd(dd,3,[Ep].[DTE])

Ensuite, en utilisant la même expression CTE dans une commande UPDATE comme suit, vous pouvez introduire les données souhaitées dans la table de la base de données cible.

;with cte as (
    select
        [Mp].[M] as M,
        [Ep].E as E,
    ROW_NUMBER() over (partition by [Mp].[M] order by abs(datediff(mi, [Mp].[DTE], [Ep].[DTE]))) as rn,
    abs(datediff(mi, [Mp].[DTE], [Ep].[DTE])) diff
    from Mp
    left join Ep 
        on [Mp].[M] = [Ep].[M] 
    WHERE [Mp].[DTE] BETWEEN dateadd(dd,-3,[Ep].[DTE]) AND dateadd(dd,3,[Ep].[DTE])
)
update [Mp]
set [E] = cte.E
from [Mp]
inner join cte on [Mp].M = cte.M and cte.rn = 1
where 
    cte.E is not null

Après l'exécution de l'instruction UPDATE, les données de la table cible sont les suivantes

J'espère que cela correspond à vos attentes