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.SOLD_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 ,SortOrder=CASE WHEN (mls.MLS_OFFICE_ID IN ('TPOI01','3162985') OR mls.TLN_FIRM_ID IN ('TPOI01','3162985')) Or mls.MLS_LISTING_ID IN (SELECT MLS_ID FROM RESI.DBO.USERS_Inventory_ExtraMLS WHERE USER_NAME = 'TPOI01' AND ADMINADDED = '1') 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] = 'TPOI01' WHERE (MLS_OFFICE_ID IN ('TPOI01','3162985') OR TLN_FIRM_ID IN ('TPOI01','3162985') ) 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] = 'TPOI01' 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 = 'TPOI01' 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 = 'TPOI01')) AND (ISNULL(RESI.DBO.USERS_BROKERAGE_UPGRADES.Active, '1') != '0') AND (mls.PROPERTY_STATE_ID = 'FL' OR mls.MLS_ID = 'FL') AND mls.Property_Type_Code IN ('H','T','R','M') 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.SOLD_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 ,SortOrder=CASE WHEN (mls.MLS_OFFICE_ID IN ('TPOI01','3162985') OR mls.TLN_FIRM_ID IN ('TPOI01','3162985')) Or mls.MLS_LISTING_ID IN (SELECT MLS_ID FROM RESI.DBO.USERS_Inventory_ExtraMLS WHERE USER_NAME = 'TPOI01' AND ADMINADDED = '1') 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] = 'TPOI01' WHERE USERS_Inventory_ExtraMLS.[USER_NAME] = 'TPOI01' 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] = 'TPOI01' 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 = 'TPOI01' 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 = 'TPOI01')) AND (ISNULL(RESI.DBO.USERS_BROKERAGE_UPGRADES.Active, '1') != '0') AND (mls.PROPERTY_STATE_ID = 'FL' OR mls.MLS_ID = 'FL') AND mls.Property_Type_Code IN ('H','T','R','M') ORDER BY SortOrder, propertyType.PROP_TYPE_ORDER , SALE_PRICE Desc