Untitled
unknown
sql
25 days ago
1.9 kB
35
Indexable
SELECT b.id, b.countryid, c.name as country_name, b.city, d.name as district_name, b.street, b.building_no, b.apartments_no, b.description, count(distinct v.customerid) as connected_cust, string_agg(v.customerid::text, ',') as customer, count(distinct CASE when active_liability(v.customerid) > 0 then v.customerid else null end) as connected_act_cust, trim(leading '0' from max(substring('0000000000'||v.apartment from '.........$'))) max_apt, round(100.0 * count( distinct CASE when active_liability(v.customerid) > 0 then v.customerid else null end) / CASE when b.apartments_no != 0 then b.apartments_no else 1 end, 2) as connection_pct, b.street|| ' '|| b.building_no||', '||b.city||', '|| c.name as bname, vs.name as building_type, cs.name as connection_status, b.connection_cost, b.month_cost, b.created_date_time, b.connected_date_time, b.modified_date_time, b.created_by, b.changed_by, b.community_id, b.cable_no, co.name as community, bp.id as project_id, bp.number as project_number, bp.completion_act_date from buildings b left join countries c on (c.id=b.countryid) left join addresses_v v on (coalesce(b.countryid, 1) = coalesce(v.countryid, 1) and b.city = v.city and b.street = v.street and b.building_no = v.building_no) left join fl_districts d on (b.district_id = d.id) left join value_sets vs on (vs.code = b.building_type and vs.type = 'building_type') left join value_sets cs on (cs.code = b.connection_status::text and cs.type = 'bld_connection_status') left join fl_buildings_communities co on (b.community_id = co.id) left join fl_buildings_projects bp on (b.project_id = bp.id) where b.id = 5113 group by b.id, b.countryid, c.name, b.city, d.name, b.street, b.building_no, b.apartments_no, b.description, vs.name, cs.name, co.name, bp.id, bp.number, bp.completion_act_date order by b.street, substring('0000'||b.building_no from '.....$')
Editor is loading...
Leave a Comment