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 42
| DECLARE @Personnes TABLE
(
person_id INT,
FormationsActives INT,
FormationsInactives INT,
ExperiencesActives INT,
ExperiencesInactives INT
);
INSERT INTO @Personnes
(
person_id,
FormationsActives,
ExperiencesActives
)
SELECT P.person_id,
COUNT(F.actif),
COUNT(E.actif)
FROM PERSONNE P
INNER JOIN FORMATION F ON P.person_id = F.person
INNER JOIN EXPERIENCE E ON P.person_id = E.personne;
WHERE F.actif = 1
AND E.actif = 1;
WITH CTE_INACTIF (persond_id, FormationInactif, ExperienceInactif) AS
(
SELECT P.person_id,
COUNT(F.actif),
COUNT(E.actif)
FROM PERSONNE P
INNER JOIN FORMATION F ON P.person_id = F.person
INNER JOIN EXPERIENCE E ON P.person_id = E.personne;
WHERE F.actif = 0
AND E.actif = 0
)
UPDATE @Personnes
SET FormationsInactives = CTE_INACTIF.FormationInactif,
ExperiencesInactives = CTE_INACTIF.ExperienceInactif
FROM (SELECT person_id FROM @Personnes) P
INNER JOIN CTE_INACTIF ON P.person_id = CTE_INACTIF.person_id;
SELECT * FROM @Personnes; |
Partager