SELECT TOP 10 MLS.LONGITUDE, MLS.LATITUDE, MLS.STREET_NAME, MLS.STREET_NUMBER, MLS.STREET_TYPE, MLS.STREET_DIRECTION, MLS.ZIP_CODE,MLS.UNIT_NUMBER, MLS.MLS_OFFICE_NAME, MLS.MLS_ID, MLS.MLS_LISTING_ID, MLS.PHOTO_IND, MLS.PHOTO_QUANTITY, MLS.PHOTO_URL, MLS.ADDRESS, CITY.CITY, MLS.PROPERTY_STATE_ID, MLS.BEDROOMS, MLS.MLS_ID, MLS.BATHS_FULL, MLS.BATHS_HALF, MLS.BATHS_TOTAL,MLS.VIRTUAL_TOUR, PROPERTYTYPE.PROP_TYPE_ORDER, PROPERTYTYPE.PROP_TYPE_DESCRIPTION, ISNULL(LISTINGPRICE, MLS.SALE_PRICE) AS SALE_PRICE, MLS.PROPERTY_TYPE_CODE, MLS.BUILDING_SQUARE_FOOTAGE, MLS.LOT_SQUARE_FOOTAGE_LAND AS ADJUSTED_SQUARE_FOOTAGE, MLS.ACRES, MLS.TOTAL_UNITS, MLS.TOTAL_BUILDINGS, MLS.TOTAL_LOTS, MLS.YEAR_BUILT, MLS.MLS_AGENT_ID, MLS.TLN_REALTOR_ID, MLS.REMARKS, MLS.MLS_AREA,ISNULL(MLS.SUBDIVISION,MLS.COMMUNITY_NAME) AS SUBDIVISION, MLS.[POOL],MLS.[WATERFRONT],MLS.[WATERVIEW],MLS.[GARAGE],MLS.[GOLF],[INLAND],MLS.[BEACH],MLS.[BOATING],MLS.[REO],[SHORT_SALE], REGIONTYPE.REGIONTYPEID, MLS.STATUS_CODE, CAST(MLS.SOLD_DATE AS DATETIME) AS SOLD_DATE, MLS.LAST_MLS_UPDATE_DATE, CAST(ISNULL(MLS.AVAILABLE_DATE,MLS.LAST_MLS_UPDATE_DATE) AS DATETIME) AS LISTED_DATE, DATEORDER=CASE WHEN (CAST(ISNULL(MLS.AVAILABLE_DATE,MLS.LAST_MLS_UPDATE_DATE) AS DATETIME) > (GETDATE()-14)) THEN 0 ELSE 1 END FROM RESI_MLS.DBO.MLS MLS INNER JOIN RESI_MLS.DBO.PROPERTYTYPE ON MLS.PROPERTY_TYPE_CODE = PROPERTYTYPE.PROPERTY_TYPE_CODE LEFT OUTER JOIN RESI_MLS.DBO.CITY ON MLS.CITY_ID = CITY.CITY_ID LEFT OUTER JOIN RESI_MLS.DBO.REGIONTYPE ON CITY.REGIONTYPEID = REGIONTYPE.REGIONTYPEID LEFT OUTER JOIN RESI.DBO.USERS_BROKERAGE_UPGRADES ON RESI.DBO.USERS_BROKERAGE_UPGRADES.MLS = MLS.MLS_LISTING_ID AND RESI.DBO.USERS_BROKERAGE_UPGRADES.[USER_NAME] = 'KAUPERREALTY' WHERE (MLS_OFFICE_ID IN ('KAUP01') OR TLN_FIRM_ID IN ('KAUP01') OR MLS_AGENT_ID IN ('3109437') OR TLN_REALTOR_ID IN ('3109437') ) AND ((MLS.PROPERTY_TYPE_CODE <> 'R' AND ISNULL(SALE_PRICE,'0') >= '0' AND ISNULL(SALE_PRICE,'0') <= '999999999') OR (MLS.PROPERTY_TYPE_CODE = 'R' AND ISNULL(SALE_PRICE,'0') >= '0' AND ISNULL(SALE_PRICE,'0') <= '999999999')) AND NOT EXISTS (SELECT CITY_ID FROM RESI.DBO.USERS_INV_SEARCH_REMOVED_CITIES WHERE [USER_NAME] = 'KAUPERREALTY' AND USERS_INV_SEARCH_REMOVED_CITIES.CITY_ID = CITY.CITY_ID) AND STATUS_CODE IN ('A','B') AND (NOT EXISTS (SELECT CITY_ID FROM RESI.DBO.USERS_INV_SEARCH_REMOVED_CITIES WHERE USER_NAME = 'KAUPERREALTY' AND USERS_INV_SEARCH_REMOVED_CITIES.CITY_ID = CITY.CITY_ID)) AND EXISTS (SELECT DISTINCT REGIONTABLE AS MLS_ID FROM REGION WHERE EXISTS (SELECT REGIONTYPEID FROM RESI.DBO.REGIONUSERS WHERE USERNAME = 'KAUPERREALTY')) AND (ISNULL(RESI.DBO.USERS_BROKERAGE_UPGRADES.ACTIVE, '1') != '0') AND (MLS.PROPERTY_STATE_ID = 'FL' OR MLS.MLS_ID = 'FL') UNION SELECT TOP 10 MLS.LONGITUDE, MLS.LATITUDE, MLS.STREET_NAME, MLS.STREET_NUMBER, MLS.STREET_TYPE, MLS.STREET_DIRECTION, MLS.ZIP_CODE,MLS.UNIT_NUMBER, MLS.MLS_OFFICE_NAME, MLS.MLS_ID, MLS.MLS_LISTING_ID, MLS.PHOTO_IND, MLS.PHOTO_QUANTITY, MLS.PHOTO_URL, MLS.ADDRESS, CITY.CITY, MLS.PROPERTY_STATE_ID, MLS.BEDROOMS, MLS.MLS_ID, MLS.BATHS_FULL, MLS.BATHS_HALF, MLS.BATHS_TOTAL,MLS.VIRTUAL_TOUR, PROPERTYTYPE.PROP_TYPE_ORDER, PROPERTYTYPE.PROP_TYPE_DESCRIPTION, ISNULL(LISTINGPRICE, MLS.SALE_PRICE) AS SALE_PRICE, MLS.PROPERTY_TYPE_CODE, MLS.BUILDING_SQUARE_FOOTAGE, MLS.LOT_SQUARE_FOOTAGE_LAND AS ADJUSTED_SQUARE_FOOTAGE, MLS.ACRES, MLS.TOTAL_UNITS, MLS.TOTAL_BUILDINGS, MLS.TOTAL_LOTS, MLS.YEAR_BUILT, MLS.MLS_AGENT_ID, MLS.TLN_REALTOR_ID, MLS.REMARKS, MLS.MLS_AREA,ISNULL(MLS.SUBDIVISION,MLS.COMMUNITY_NAME) AS SUBDIVISION, MLS.[POOL],MLS.[WATERFRONT],MLS.[WATERVIEW],MLS.[GARAGE],MLS.[GOLF],[INLAND],MLS.[BEACH],MLS.[BOATING],MLS.[REO],[SHORT_SALE], REGIONTYPE.REGIONTYPEID, MLS.STATUS_CODE, CAST(MLS.SOLD_DATE AS DATETIME) AS SOLD_DATE, MLS.LAST_MLS_UPDATE_DATE, CAST(ISNULL(MLS.AVAILABLE_DATE,MLS.LAST_MLS_UPDATE_DATE) AS DATETIME) AS LISTED_DATE, DATEORDER=CASE WHEN (CAST(ISNULL(MLS.AVAILABLE_DATE,MLS.LAST_MLS_UPDATE_DATE) AS DATETIME) > (GETDATE()-14)) THEN 0 ELSE 1 END FROM USERS_INVENTORY_EXTRAMLS INNER JOIN RESI_MLS.DBO.MLS MLS ON USERS_INVENTORY_EXTRAMLS.MLS_ID = MLS.MLS_LISTING_ID INNER JOIN RESI_MLS.DBO.PROPERTYTYPE ON MLS.PROPERTY_TYPE_CODE = PROPERTYTYPE.PROPERTY_TYPE_CODE LEFT OUTER JOIN RESI_MLS.DBO.CITY ON MLS.CITY_ID = CITY.CITY_ID LEFT OUTER JOIN RESI_MLS.DBO.REGIONTYPE ON CITY.REGIONTYPEID = REGIONTYPE.REGIONTYPEID LEFT OUTER JOIN RESI.DBO.USERS_BROKERAGE_UPGRADES ON RESI.DBO.USERS_BROKERAGE_UPGRADES.MLS = MLS.MLS_LISTING_ID AND RESI.DBO.USERS_BROKERAGE_UPGRADES.[USER_NAME] = 'KAUPERREALTY' WHERE USERS_INVENTORY_EXTRAMLS.[USER_NAME] = 'KAUPERREALTY' AND ((MLS.PROPERTY_TYPE_CODE <> 'R' AND ISNULL(SALE_PRICE,'0') >= '0' AND ISNULL(SALE_PRICE,'0') <= '999999999') OR (MLS.PROPERTY_TYPE_CODE = 'R' AND ISNULL(SALE_PRICE,'0') >= '0' AND ISNULL(SALE_PRICE,'0') <= '999999999')) AND NOT EXISTS (SELECT CITY_ID FROM RESI.DBO.USERS_INV_SEARCH_REMOVED_CITIES WHERE [USER_NAME] = 'KAUPERREALTY' AND USERS_INV_SEARCH_REMOVED_CITIES.CITY_ID = CITY.CITY_ID) AND STATUS_CODE IN ('A','B') AND (NOT EXISTS (SELECT CITY_ID FROM RESI.DBO.USERS_INV_SEARCH_REMOVED_CITIES WHERE USER_NAME = 'KAUPERREALTY' AND USERS_INV_SEARCH_REMOVED_CITIES.CITY_ID = CITY.CITY_ID)) AND EXISTS (SELECT DISTINCT REGIONTABLE AS MLS_ID FROM REGION WHERE EXISTS (SELECT REGIONTYPEID FROM RESI.DBO.REGIONUSERS WHERE USERNAME = 'KAUPERREALTY')) AND (ISNULL(RESI.DBO.USERS_BROKERAGE_UPGRADES.ACTIVE, '1') != '0') AND (MLS.PROPERTY_STATE_ID = 'FL' OR MLS.MLS_ID = 'FL') ORDER BY PROPERTYTYPE.PROP_TYPE_ORDER , SALE_PRICE ASC