SELECT ca.addresstypecode,
a.accountid accountid,
       a.accountnumber [accountnumber],
       a.accountratingcode [accountsegment],
       a.address1_addresstypecode,
       a.address1_city,
       a.address1_country,
       a.address1_line1,
       a.address1_line2,
       a.address1_line3,
       a.address1_postalcode,
       a.address1_stateorprovince,
       a.address2_city,
       a.address2_country,
       a.address2_line1,
       a.address2_line2,
       a.address2_line3,
       a.address2_postalcode,
       a.address2_stateorprovince,
       a.creditlimit,
       a.creditonhold,
       a.customertypecode,
       a.industrycode,
       a.name [name],
       a.ownerid,
       a.parentaccountid,
       a.paymenttermscode,
       a.sik_billingemail,
       a.sik_billingfax,
       a.sik_billingphone,
       a.sik_copyprimaryadd,
       a.sik_deliverymethodid,
       a.sik_warehouseid,
       a.telephone1 [main phone],
       ca.customeraddressid,
       ca.addressnumber,
       ca.city ca_city,
       ca.country ca_country,
       ca.line1 ca_line1,
       ca.line2 ca_line2,
       ca.line3 ca_line3,
       ca.postalcode ca_postalcode,
       ca.sik_addressemail ca_email,
       ca.sik_scribeexternalid [ca_location_id],
       ca.stateorprovince ca_state,
       ca.telephone1 ca_telephone,
       dm.sik_methodid,
       osm.localizedlabel [address type],
       pa.name [parentaccountname],
       w.sik_code [warehouse code],
       w.sik_name [warehouse name]

FROM account a
LEFT JOIN account pa ON a.parentaccountid = pa.accountid
JOIN customeraddress ca ON a.accountid = ca.parentid
JOIN optionsetmetadata osm ON osm.entityname = 'customeraddress'
    AND osm.optionsetname = 'addresstypecode'
    AND ca.addresstypecode = osm.[option]
LEFT JOIN sik_warehouse w ON a.sik_warehouseid = w.sik_warehouseid
LEFT JOIN sik_deliverymethods dm ON a.sik_deliverymethodid = dm.sik_deliverymethodsid

WHERE a.accountnumber IN ('58398',
    '4350',
    '5076',
    '4310',
    '15847')
    AND ca.addressnumber > 2
    AND ca.jms_status = 100000000
    AND ca.addressnumber > (
        SELECT TOP 1 ca_sub1.addressnumber
        FROM customeraddress ca_sub1
        JOIN account a_sub1 ON ca_sub1.parentid = a_sub1.accountid
 **WHERE a.accountnumber IN ('58398',
    '4350',
    '5076',
    '4310',
    '15847')
    AND ca.addressnumber > 2
    AND ca.jms_status = 100000000**
)
ORDER BY [accountnumber],
         ca.addressnumber ASC