officeToResConversions.sql

SELECT p.permission_id AS permission_id,
       COUNT(prl.permission_id) AS branches,
       SUM(erl.number_of_units) AS existingSocialHousing,
       SUM(prl.number_of_units) AS proposedSocialHousing,
       status_rc,
       borough_ref,
       b.name AS borough_name,
       permission_date,
       permission_lapses_date,
       decision_agency_rc,
       p.descr,
       sec_add_obj_name,
       prim_add_obj_name,
       street,
       town,
       post_code,
       easting,
       northing,
       p.completed_date,
       p.superseded_date
FROM 
(
  (
    (
       (
       app_ldd.ld_permissions AS p LEFT JOIN app_ldd.ld_boroughs AS b
         ON p.borough_id
            =b.borough_id
       )
       LEFT JOIN app_ldd.ld_ref_codes AS rc
         ON p.permission_type_rc
            =rc.code
     ) LEFT JOIN app_ldd.ld_prop_res_lines AS prl_super
         ON p.permission_id
            =prl_super.superseded_permission_id
  ) LEFT JOIN app_ldd.ld_exist_res_lines AS erl
      ON p.permission_id
        =erl.permission_id
) LEFT JOIN app_ldd.ld_prop_res_lines AS prl
    ON p.permission_id
      =prl.permission_id

WHERE status_rc != 'DELETED' AND (p.descr ILIKE '%prior%B1%to%C3%')
GROUP BY p.permission_id,
       status_rc,
       borough_ref,
       b.name,
       permission_date,
       permission_lapses_date,
       decision_agency_rc,
       p.descr,
       sec_add_obj_name,
       prim_add_obj_name,
       street,
       town,
       post_code,
       easting,
       northing,
       p.completed_date,
       p.superseded_date
HAVING COUNT(prl_super.permission_id) = 0
        AND ((SUM(erl.number_of_units) > 0) OR (SUM(prl.number_of_units) > 0))
;