| sql | | string | SELECT * FROM ( SELECT RowNumber=ROW_NUMBER() OVER (ORDER BY lastname desc), Agents.AccessLevel, Agents.LastLogin, Agents.DateTime, Agents.publicid, Agents.UserID, Agents.Password, Agents.firstname, Agents.MiddleName, Agents.lastname, Agents.La_Name, Agents.AKA, Agents.Cell, Agents.Phone, Agents.Fax, Agents.Email, Agents.Team, Agents.Role, Agents.Title, Agents.Extension, Agents.Certifications, Agents.WebsiteAddress, Agents.OfficeID, Agents.Image_List, Agents.image_data, Agents.ProfileDisplay, Agents.VideoProfile, Agents.ProfileText, Agents.Status, Agents.Website, Agents.LicenseNumber, '' As Languages, '/assets/img/nophoto/nophoto-agents.jpg' AS ImagePath, '' AS url, 0 AS SortOrder, '' AS DomainName, '' AS LogoPath, '' AS DarkLogoPath, '' AS LogoPathSmall, '' AS BioPath, 'F' AS HasListings, 'F' AS HasPropertyManagementListings, 'F' AS HasBlog, 'F' AS TeamHasListings, 'F' AS TeamHasPropertyManagementListings, Offices.CompanyName, Offices.OfficeName, Offices.OfficePhone, Offices.OfficeFax, Offices.OfficeTollFree, Offices.OfficeAddress, Offices.OfficeCity, Offices.OfficeState, Offices.OfficeZip, Offices.SerialNumber, Offices.OfficeDomain, Offices.Lat as officelat, Offices.lon as officelon, Offices.officelogodark, Offices.officelogolight, Agents_Links.twitter, Agents_Links.facebook, Agents_Links.instagram, Agents_Links.linkedin, Agents_Links.googleplus, Agents_Links.pinterest, Agents_Links.youtube, Agents_Links.yelp, Agents_Links.blog, Agents_Teams.AgentTeamID, Agents_Teams.PublicID as teamleaderpublicid, Agents_Teams.TeamName as teamname, Agents_Teams.Phone as teamphone, Agents_Teams.Fax as teamfax, Agents_Teams.Cell as teamcell, Agents_Teams.image_list as TeamImage_List, Agents_Teams.WebsiteAddress as TeamWebsiteAddress, Agents_Preferences.zillow_reviews, Agents_Preferences.zillow_email, Agents_Preferences.realsatisfied_reviews, Agents_Preferences.realsatisfied_vanitykey, Agents_Preferences.ShowTestimonialTree AS testimonialtree_reviews, Agents_Preferences.ShowTestimonialTreeType AS testimonialtree_type, Agents_Preferences.testimonialtree_username AS testimonialtree_userId, Agents_Preferences.overrideofficemls, Agents_Preferences.overrideofficemlsid, Agents_Preferences.overrideofficemlsid_list, Agents_Teams_Members.DeactiveDate, companies.dark_logo as company_dark_logo, companies.light_logo as company_light_logo, companies.platform_logo as company_platform_logo, companies.islogolocked as is_company_logo_locked FROM Agents WITH (NOLOCK) LEFT OUTER JOIN agents_preferences WITH (NOLOCK) ON agents_preferences.publicid=Agents.publicid LEFT OUTER JOIN Agents_Links WITH (NOLOCK) ON Agents_Links.publicid=Agents.publicid INNER JOIN Offices WITH (NOLOCK) ON Offices.OfficeID=Agents.OfficeID INNER JOIN companies WITH (NOLOCK) ON companies.companyNumber=Offices.companyNumber OUTER APPLY ( SELECT TOP 1 * FROM Agents_Teams_Members WITH (NOLOCK) WHERE Agents_Teams_Members.publicid=Agents.publicid and agentteamid in ( select agentteamid from agents_teams where status='A' and Agents_Teams_Members.DeactiveDate='' ) ORDER BY Agents_Teams_Members.LastUpdateDateTime DESC ) Agents_Teams_Members OUTER APPLY ( SELECT TOP 1 * FROM Agents_Teams WITH (NOLOCK) WHERE Agents_Teams.AgentTeamID=Agents_Teams_Members.AgentTeamID AND Agents_Teams.Status='A' ) Agents_Teams WHERE (Agents.Status='A') AND ( Agents.publicid in ('626601') ) ) dtData WHERE dtData.RowNumber BETWEEN 1 AND 99999 |
|
