Untitled
unknown
sql
20 days ago
2.6 kB
22
Indexable
SELECT CASE WHEN b.id IS NULL THEN (SELECT id FROM buildings WHERE buildings.street = v.street AND buildings.building_no = v.building_no LIMIT 1) ELSE b.id END as id, v.countryid, c.name as country_name, v.city, d.name as district_name, v.street, v.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, v.street|| ' '|| v.building_no||', '||v.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( SELECT customer_addresses.customer_id AS customerid, a.flat AS apartment, a.country_id AS countryid, a.city, a.street, a.house AS building_no, cc.name AS country FROM customer_addresses customer_addresses JOIN customers c ON c.id = customer_addresses.customer_id JOIN addresses a ON a.id = customer_addresses.address_id LEFT JOIN countries cc ON a.country_id = cc.id WHERE c.deleted = 0 AND a.street='Šv. Ignoto g.' AND a.house='3' ) AS v LEFT JOIN buildings b 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 countries c on (c.id=v.countryid) 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) GROUP BY b.id, v.countryid, v.street, v.building_no, v.city, c.name, d.name, b.apartments_no, b.description, vs.name, cs.name, co.name, bp.id, bp.number, bp.completion_act_date
Editor is loading...
Leave a Comment