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
|
WITH CTE AS (
SELECT
[Code rayon]
,MIN([Référence externe]) OVER (PARTITION BY rea.[N° article], rea.Company, [Type référence externe]) as MinRef
,MAX([Référence externe]) OVER (PARTITION BY rea.[N° article], rea.Company, [Type référence externe]) as MaxRef
,RANK() OVER(
PARTITION BY rea.[N° article], rea.Company
ORDER BY
CASE [Type référence externe]
WHEN 1 THEN 1
WHEN 13 THEN 2
WHEN 14 THEN 3
END
) AS Rang
FROM [BDD].[Article] AS art
LEFT OUTER JOIN [BDD].[Référence externe article] AS rea
ON rea.[N° article] = art.[N°]
AND rea.Company = art.Company
AND [Type référence externe] IN (1,13,14)
)
UPDATE CTE
SET [Code rayon] =
CASE
WHEN MinRef = MaxRef THEN MinRef
WHEN MinRef <> MaxRef THEN 'Plusieurs résultats possibles'
ELSE 'Non renseigné'
END
WHERE Rang = 1 |
Partager