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
| select distinct a.nom,
a.prenom,
convert(char(10),a.date_naiss,103) as date_naissance,
a.matricule/10 as matricule,
case a.no_ss when '7' then ''
else a.no_ss end as numero_insee,
isnull (case b.centre when '001' then '1'
when '08' then 'CTA46'
when '999' then 'AUTRE'
end,'') as centre1,
b.libel_centre as nom_centre1,
case b.TYPE_CARRIERE when'1' then (case b.Statut when 'SANSSTATUT' then 'C' else 'P' end)
when '3' then (case b.Statut when 'ELU' then 'E' else 'A' end)
when '2' then 'V'
end as statut,
case b.TYPE_CARRIERE when'1' then (case b.Statut when 'SANSSTATUT' then 'Volontaire civil' else 'Professionnel' end)
when '3' then (case b.Statut when 'ELU' then 'Elu' else 'PATS' end)
when '2' then 'Volontaire'
end as nom_statut,
isnull ((select max(c.V1DATE) from DDSIS460001.dbo.M_VISAGE c where a.matricule=c.MATRICULE group by c.MATRICULE), '') as date_visite,
'' as Heure,
c.VICODE
from DDSIS460001.dbo.agents a
left join DDSIS460001.dbo.carrieres b on a.matricule=b.matricule
left join DDSIS460001.dbo.M_VISAGE c on a.matricule=c.MATRICULE |
Partager