add sanitation mailing list as a report

[laurahaley]

we get requests for a city sanitation mailing list several times a year and it would be nice to have it as a standing report on master address. The oracle query I used before is attached. Unlike other address reports where we want both the base address and the subunit addresses attached, if there are current subunits, we want only the units, not the base address for a mailing list.

• We can't yet use the mailable flag as I'm not sure it is complete.
• We need the location id to tie it back to the systems that sanitation and utilities are using for management and billing.
  • only want it included if it is a current address/subunit location

`--new trash address query removing retired subunits with current locations and adding lat lon select al.location_id, al.street_address_id, -- al.subunit_id, -- als.status_code as LocStatus, -- mals.description as FullAddStatus, --ac.addresscount, ma.street_number "NUMBER", ma.street_number_suffix "NUMBER_SUFFIX", rtrim(msn.street_direction_code) "DIR", msn.street_name "STREET_NAME" , msn.street_type_suffix_code "SUFFIX", msn.post_direction_suffix_code "POSTDIR", rtrim(masub.sudtype || ' ' || masub.subunit_identifier) "SUBUNIT", ma.city, ma.state, ma.zip, ma.zipplus4, rtrim(rtrim(ma.street_number || ' ' || ma.street_number_suffix) || ' ' || rtrim(msn.street_direction_code) || ' ' || msn.street_name || ' ' || msn.street_type_suffix_code || ' ' || msn.post_direction_suffix_code) || ' ' || rtrim(masub.sudtype || ' ' || masub.subunit_identifier) as FULL_ADDRESS, rtrim( ma.city || ', ' || ma.state || ' ' || ma.zip) as CITY_STATE_ZIP, masan.trash_pickup_day, masan.recycle_week, ma.latitude, ma.longitude

    from eng.address_location al
        join (select street_address_id, count(location_id) addresscount from eng.address_location group by street_address_id) ac on al.street_address_id = ac.street_address_id
        join master_address.mast_address_latest_status mals on al.street_address_id = mals.street_address_id
        join master_address.latest_location_status als on al.location_id = als.location_id
        join eng.mast_address ma on al.street_address_id = ma.street_address_id
        join eng.mast_street ms on ma.street_id = ms.street_id
        join eng.mast_street_names msn on ms.street_id = msn.street_id
        left join eng.mast_address_subunits masub on al.subunit_id = masub.subunit_id
        left join eng.mast_address_sanitation masan on al.street_address_id = masan.street_address_id

    --remove inactive addresses for current locations - same for both sides
            where al.active = 'Y'
            -- only get single address addresses - different for single address side
            and (al.subunit_id is NULL and ac.addresscount = 1)
            -- only get current single addresses  - different for single address side
            and mals.status_code = '1'
            -- get current street name - same for both sides
            and msn.street_name_type = 'STREET'
            -- only get trash addresses  - same for both sides
    and masan.trash_pickup_day is not null

                -- Tester that have mixed status subunits - same for both sides
                --and al.street_address_id = 18532
                --and al.street_address_id = 4642

union

select al.location_id, al.street_address_id, -- al.subunit_id, -- als.status_code as LocStatus, -- lss.description as FullAddStatus, -- ac.addresscount, ma.street_number "NUMBER", ma.street_number_suffix "NUMBER_SUFFIX", rtrim(msn.street_direction_code) "DIR", msn.street_name "STREET_NAME" , msn.street_type_suffix_code "SUFFIX", msn.post_direction_suffix_code "POSTDIR", rtrim(masub.sudtype || ' ' || masub.subunit_identifier) "SUBUNIT", ma.city, ma.state, ma.zip, ma.zipplus4, rtrim(rtrim(ma.street_number || ' ' || ma.street_number_suffix) || ' ' || rtrim(msn.street_direction_code) || ' ' || msn.street_name || ' ' || msn.street_type_suffix_code || ' ' || msn.post_direction_suffix_code) || ' ' || rtrim(masub.sudtype || ' ' || masub.subunit_identifier) as FULL_ADDRESS, rtrim( ma.city || ', ' || ma.state || ' ' || ma.zip) as CITY_STATE_ZIP, masan.trash_pickup_day, masan.recycle_week, masub.latitude, masub.longitude

    from eng.address_location al
        join (select street_address_id, count(location_id) addresscount from eng.address_location group by street_address_id) ac on al.street_address_id = ac.street_address_id
        join master_address.latest_subunit_status lss on al.subunit_id = lss.subunit_id
        join master_address.latest_location_status als on al.location_id = als.location_id
        join eng.mast_address ma on al.street_address_id = ma.street_address_id
        join eng.mast_street ms on ma.street_id = ms.street_id
        join eng.mast_street_names msn on ms.street_id = msn.street_id
        left join eng.mast_address_subunits masub on al.subunit_id = masub.subunit_id
        left join eng.mast_address_sanitation masan on al.street_address_id = masan.street_address_id

   --remove inactive addresses for current locations - same for both sides
        where al.active = 'Y'
            -- only get multi unit addresses - different for subunit address side
        and al.subunit_id is not NULL
            -- only get current multi unit addresses - different for subunit address side
        and lss.status_code = '1'
            -- get current street name - same for both sides
            and msn.street_name_type = 'STREET'
            -- only get trash addresses  - same for both sides
    and masan.trash_pickup_day is not null

                -- Tester that have mixed status subunits - same for both sides
                --and al.street_address_id = 18532
                --and al.street_address_id = 4642

        order by "STREET_NAME", "SUFFIX", "DIR", "POSTDIR", "NUMBER", "SUBUNIT"

`

[laurahaley]

Here is the sql file reportsanitationmailinglistbestsql.txt