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 43 44
|
SELECT
CONVERT(Varchar(10),DateEstim,103)AS dateestim,
LEFT(ISNULL(AG.Libelle_agence,''),3) AS refagence,
LEFT(ISNULL(LTB.Libelle,''),1) AS Type,
'<nobr>' + REPLACE(LEFT(
CONVERT(VARCHAR(15),CAST(BI.PrixEstime AS MONEY),1),
LEN(CONVERT(VARCHAR(15),CAST(BI.PrixEstime AS MONEY),1))-3)
,',',' ') + ' €</nobr>' AS prixEstim,
ISNULL(LOB.Libelle,'') AS originebien,
ISNULL(Neg.Prenom + ' ' + Neg.Nom,'') AS Id_Nego,
ISNULL(CIV.Libelle,'') + ' ' + ISNULL(CLI.Nom,'') + ' ' + ISNULL(CLI.Prenom,'') + '/' +
ISNULL(CLI.Telephone,'') + '/' + ISNULL(CLI.portable,'') as refproprio,
adressebien AS adressebien,
CO.Ville + ' ' + CO.Quartier AS quartier,
nbpiece AS nbpiece,
NoteConfident as noteconfident,
BI.id_bien AS id_bien,
BI.flag_bien AS flag_bien,
BI.etat_bien AS etat_bien,
BI.Prixestime as PrixFAIfiltre,
ISNULL(LTT.Libelle,'') AS transac,
BI.PrixEstime as PrixEstimTri,
typemandat AS TypeMandat,
ISNULL(CIV.Libelle,'') + ' ' + ISNULL(CLI.Nom,'') + ' ' + ISNULL(CLI.Prenom,'') + '/' +
ISNULL(CLI.Telephone,'') + '/' + ISNULL(CLI.portable,'') as CliTel,
ISNULL(CLI.Nom,'') + ' ' + ISNULL(CLI.Prenom,'') as CliRech,
DateEstim as DEstimTri,
datemodification AS datemodif,
CO.Ville AS VilleBien,
BI.Quartier AS QuartierRech
FROM Biens BI
LEFT JOIN LISTE LOB ON LOB.Valeur = BI.OrigineBien and LOB.Nom_liste = 'Origine Bien'
LEFT JOIN LISTE LTB ON LTB.Valeur = BI.Typebien and LTB.nom_liste = 'Type Bien'
LEFT JOIN LISTE LTT ON LTT.Valeur = BI.transac and LTT.nom_liste = 'Type Transaction'
LEFT JOIN (SELECT Id_Bien, MIN(Id_Client) AS Id_Client, Type_Client FROM Client_Bien WHERE Type_Client = 1
GROUP BY Id_Bien, Type_Client) CB ON CB.Id_Bien = BI.Id_Bien
LEFT JOIN CLIENTS CLI ON CLI.Id_CLient = CB.Id_client
LEFT JOIN LISTE CIV ON CIV.Valeur = CLI.Civilite AND CIV.Nom_Liste = 'Civilite'
LEFT JOIN Communes CO ON CO.Id_Commune = BI.quartier
LEFT JOIN Agence AG ON AG.Id_Agence = BI.Refagence
LEFT JOIN ADMIN Neg ON Neg.Id_Admin = BI.Id_Nego
WHERE (flag_bien = 2) |
Partager