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 45 46 47 48
|
SELECT imm.NUMERO_DOSSIER
, imm.LETTRE_DOSSIER
, imm.DATE_DEBUT
, dist.NOM_DISTRICT
, comm.NOM_COMMUNE
, statut.TYPE
, travaux.TYPE
, COUNT(CASE log.NOMBRE_PIECE WHEN 1 THEN log.NOMBRE_PIECE END) AS 1p
, COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 1 THEN log.SURFACE_NETTE END), 2), 0) AS m2
, COALESCE(SUM(CASE log.NOMBRE_PIECE WHEN 1 THEN coutlog1.COUT END), 0) AS Cout
, COUNT(CASE log.NOMBRE_PIECE WHEN 2 THEN log.NOMBRE_PIECE END) AS 2p
, COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 2 THEN log.SURFACE_NETTE END), 2), 0) AS m2
, COALESCE(SUM(CASE log.NOMBRE_PIECE WHEN 2 THEN coutlog1.COUT END), 0) AS Cout
, COUNT(CASE log.NOMBRE_PIECE WHEN 3 THEN log.NOMBRE_PIECE END) AS 3p
, COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 3 THEN log.SURFACE_NETTE END), 2), 0) AS m2
, COALESCE(SUM(CASE log.NOMBRE_PIECE WHEN 3 THEN coutlog1.COUT END), 0) AS Cout
, COUNT(CASE log.NOMBRE_PIECE WHEN 4 THEN log.NOMBRE_PIECE END) AS 4p
, COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 4 THEN log.SURFACE_NETTE END), 2), 0) AS m2
, COALESCE(SUM(CASE log.NOMBRE_PIECE WHEN 4 THEN coutlog1.COUT END), 0) AS Cout
, COUNT(CASE log.NOMBRE_PIECE WHEN 5 THEN log.NOMBRE_PIECE END) AS 5p
, COALESCE(SUM(CASE log.NOMBRE_PIECE WHEN 5 THEN coutlog1.COUT END), 0) AS Cout
, COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 5 THEN log.SURFACE_NETTE END), 2), 0) AS m2
, COUNT(CASE WHEN log.NOMBRE_PIECE BETWEEN 1 AND 5 THEN log.NOMBRE_PIECE END) AS Nb_log
, ROUND(SUM(CASE WHEN log.NOMBRE_PIECE BETWEEN 1 AND 5 THEN log.SURFACE_NETTE END), 2) AS Surface
, SUM(CASE WHEN log.NOMBRE_PIECE BETWEEN 1 AND 5 THEN coutlog1.COUT END) AS Cout
FROM IMMEUBLE AS imm
JOIN COMMUNE AS comm
ON comm.ID = imm.COMMUNE_ID_FK
JOIN DISTRICT AS dist
ON dist.ID = comm.DISTRICT_ID_FK
JOIN TYPE_STATUT_IMMEUBLE AS statut
ON statut.ID = TYPE_STATUT_IMMEUBLE_ID_FK
JOIN TYPE_NATURE_TRAVAUX AS travaux
ON travaux.ID = TYPE_NATURE_TRAVAUX_ID_FK
JOIN LOGEMENT AS log
ON log.IMMEUBLE_ID_FK = imm.ID
INNER JOIN COUT_LOGEMENT AS coutlog1
ON coutlog1.LOGEMENT_ID_FK = log.ID
LEFT JOIN COUT_LOGEMENT AS coutlog2
ON coutlog2.LOGEMENT_ID_FK = log.ID
AND coutlog2.DEBUT_PERIODE > coutlog1.DEBUT_PERIODE
WHERE log.NOMBRE_PIECE BETWEEN 1 AND 5
AND coutlog2.DEBUT_PERIODE IS NULL
GROUP BY imm.NUMERO_DOSSIER
, imm.LETTRE_DOSSIER
ORDER BY imm.NUMERO_DOSSIER ASC
, imm.LETTRE_DOSSIER ASC; |
Partager