1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
| ;WITH
TH AS
(
SELECT [Ident_FT Bénéficiaire]
, [Entité Bénéficiaire]
, MAX([Date réalisée]) AS Date
, [Ident_FT ManHab]
, [Nom ManHab]
, [Prenom ManHab]
, [Email ManHab]
, Utilisateur
FROM dbo.[Habilitation stagiaires]
WHERE [Entité Bénéficiaire] IS NOT NULL
AND [Ident_FT ManHab] IS NOT NULL
AND [Nom ManHab] IS NOT NULL
AND [Prenom ManHab] IS NOT NULL
AND [Email ManHab] IS NOT NULL
AND [Ident_FT Bénéficiaire] IS NOT NULL
AND Utilisateur IS NOT NULL
GROUP BY [Ident_FT Bénéficiaire]
, [Entité Bénéficiaire]
, [Ident_FT ManHab]
, [Nom ManHab]
, [Prenom ManHab]
, [Email ManHab]
, Utilisateur
)
UPDATE dbo.[Revue Ident S2]
SET Utilisateur = RIS2.Utilisateur
, [Ident_FT MANHAB] = TH.[Ident_FT ManHab]
, [Nom ManHab] = RIS2.[Nom ManHab]
, [Prenom ManHab] = RIS2.[Prenom ManHab]
, [Email ManHab] = RIS2.[Email ManHab]
, Service = TH.[Entité Bénéficiaire]
, [Date MAJ] = TH.Date
FROM TH
LEFT JOIN dbo.[Revue Ident S2] AS RIS2
ON TH.[Ident_FT Bénéficiaire] = RIS2.ident
WHERE RIS2.ident = TH.[Ident_FT Bénéficiaire]
AND RIS2.[Ident_FT MANHAB] <> TH.[Ident_FT ManHab]
AND RIS2.[Date MAJ] IS NOT NULL |
Partager