1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
| SELECT TOP 100 tClient.Sexe,tClient.Nom,tClient.Prenom,Activite,Referent_Nom,tDossier.Dt_Debut,tDossier.Dt_Fin,
(SELECT TOP 1 Prenom + ' ' + Nom FROM
(SELECT Assistance_ID,Usager_ID,Dossier_ID FROM tDossier_Assistance )tD_A
INNER JOIN (SELECT TOP 1 Assistance_ID FROM tAssistance WHERE Afficher_Sur_Rapport=1)tA ON tD_A.Assistance_ID=tA.Assistance_ID AND tD_A.Dossier_ID=tDossier.Dossier_ID
INNER JOIN tUsager ON tUsager.Usager_ID=tD_A.Usager_ID
)
As Usager_Nom,tClient_Adresse.Adresse_ID,
Adresse_Civique,Ville,tMunicipalite.Nom As Municipalite_Nom,Cd_Postal,Etat,Pays
FROM (SELECT * FROM tDossier )tDossier
INNER JOIN (SELECT Client_ID,Sexe,Nom,Prenom FROM tClient)tClient ON tClient.Client_ID = tDossier.Client_ID
INNER JOIN (SELECT Nom As Activite, Activite_ID FROM tActivite )tA ON tA.Activite_ID = tDossier.Activite_ID
INNER JOIN (SELECT Referent_ID,Nom As Referent_Nom FROM tReferent )tR ON tR.Referent_ID = tDossier.Referent_ID
LEFT JOIN tClient_Adresse ON tClient.Client_ID = tClient_Adresse.Client_ID
LEFT JOIN (SELECT TOP 1 Adresse_ID,Adresse_Civique,tAdresse.Ville,Municipalite_ID,Cd_Postal,Etat,Pays FROM tAdresse ORDER BY Principal DESC)vAdresse ON tClient_Adresse.Adresse_ID=vAdresse.Adresse_ID
LEFT JOIN tMunicipalite ON tMunicipalite.Municipalite_ID = vAdresse.Municipalite_ID |
Partager