SQL: SELECT TOP 1000 Rental.id AS Rental_id, Rental.CustomerId AS Rental_CustomerId, Rental.RentalTypeId AS Rental_RentalTypeId, Rental.ContentId AS Rental_ContentId, Rental.AddressId AS Rental_AddressId, Rental.SoftwareId AS Rental_SoftwareId, Rental.StaffId AS Rental_StaffId, Rental.Title AS Rental_Title, Rental.Teaser AS Rental_Teaser, Rental.Bedrooms AS Rental_Bedrooms, Rental.Bathrooms AS Rental_Bathrooms, Rental.Sleeps AS Rental_Sleeps, Rental.Code AS Rental_Code, Rental.SoftwareCode AS Rental_SoftwareCode, Rental.UrlTitle AS Rental_UrlTitle, Rental.Priority AS Rental_Priority, Rental.EnabledFlag AS Rental_EnabledFlag, Rental.DateCreated AS Rental_DateCreated, Rental.DateUpdated AS Rental_DateUpdated, Rental.DateDisplayed AS Rental_DateDisplayed, Address.id AS Address_id, Address.AddressTypeId AS Address_AddressTypeId, Address.Address1 AS Address_Address1, Address.Address2 AS Address_Address2, Address.City AS Address_City, Address.StateId AS Address_StateId, Address.CountryId AS Address_CountryId, Address.ZipCode AS Address_ZipCode, Address.Latitude AS Address_Latitude, Address.Longitude AS Address_Longitude, Address.DateCreated AS Address_DateCreated, Address.DateUpdated AS Address_DateUpdated, AddressType.id AS AddressType_id, AddressType.AddressType AS AddressType_AddressType, Content.id AS Content_id, Content.LanguageId AS Content_LanguageId, Content.HtmlContent AS Content_HtmlContent, Content.PageTitle AS Content_PageTitle, Content.MetaDesc AS Content_MetaDesc, Content.MetaKeywords AS Content_MetaKeywords, Content.DateCreated AS Content_DateCreated, Content.DateUpdated AS Content_DateUpdated, Customer.id AS Customer_id, Customer.AccountId AS Customer_AccountId, Customer.AddressId AS Customer_AddressId, Customer.Company AS Customer_Company, Customer.LegalName AS Customer_LegalName, Customer.Email AS Customer_Email, Customer.EnabledFlag AS Customer_EnabledFlag, Customer.ApiKey AS Customer_ApiKey, Customer.DateCreated AS Customer_DateCreated, Customer.DateUpdated AS Customer_DateUpdated, RentalType.id AS RentalType_id, RentalType.RentalType AS RentalType_RentalType, Software.id AS Software_id, Software.CustomerId AS Software_CustomerId, Software.SoftwareTypeId AS Software_SoftwareTypeId, Software.ApiUrl AS Software_ApiUrl, Software.Code AS Software_Code, Software.Username AS Software_Username, Software.Password AS Software_Password, Software.ClientId AS Software_ClientId, Software.ApiKey AS Software_ApiKey, Software.Title AS Software_Title, Software.AutoSyncFlag AS Software_AutoSyncFlag, Software.DateCreated AS Software_DateCreated, Software.DateUpdated AS Software_DateUpdated, Staff.id AS Staff_id, Staff.CustomerId AS Staff_CustomerId, Staff.StaffTypeId AS Staff_StaffTypeId, Staff.ContactId AS Staff_ContactId, Staff.Password AS Staff_Password, Staff.EnabledFlag AS Staff_EnabledFlag, Staff.AccountAdminFlag AS Staff_AccountAdminFlag, Staff.DateCreated AS Staff_DateCreated, Staff.DateUpdated AS Staff_DateUpdated, Staff.DateDisplayed AS Staff_DateDisplayed, Contact.id AS Contact_id, Contact.Company AS Contact_Company, Contact.FirstName AS Contact_FirstName, Contact.LastName AS Contact_LastName, Contact.Title AS Contact_Title, Contact.Email AS Contact_Email, Contact.DateBirth AS Contact_DateBirth, Contact.DateAnniversary AS Contact_DateAnniversary, Contact.DateCreated AS Contact_DateCreated, Contact.DateUpdated AS Contact_DateUpdated, (SELECT CAST(AVG(CAST(Rating As Decimal(4, 1))) As Decimal(4,1)) FROM Review WHERE RentalId = Rental.id AND EnabledFlag = 1) As Rental_Rating, (SELECT COUNT(*) FROM RentalMedia INNER JOIN Media ON RentalMedia.MediaId = Media.id WHERE RentalMedia.RentalId = Rental.id AND Media.MediaTypeId IN (1)) AS Rental_ImageCount, (SELECT COUNT(*) FROM RentalMedia INNER JOIN Media ON RentalMedia.MediaId = Media.id WHERE RentalMedia.RentalId = Rental.id AND Media.MediaTypeId IN (10, 11)) AS Rental_VideoCount, (SELECT COUNT(*) FROM Review WHERE Review.RentalId = Rental.id) AS Rental_ReviewCount, MinRateNight.MinNight As Rental_MinNightRate, AvgRateNight.AvgNight As Rental_AvgNightRate, MinRateWeek.MinWeek As Rental_MinWeekRate, MinRateMonth.MinMonth As Rental_MinMonthRate, (CASE WHEN MinRateNight.MinNight IS NULL AND MinRateWeek.MinWeek IS NULL THEN 9999999 WHEN MinRateNight.MinNight IS NOT NULL THEN MinRateNight.MinNight ELSE MinRateWeek.MinWeek / 7 END) AS Rental_MinRateSorted, (SELECT TOP 1 TurnDay FROM TurnDay WHERE TurnDay.RentalId = Rental.id AND '3/10/2018 00:00:00' BETWEEN DateStarts AND DateEnds) As Rental_TurnDay, Special.Code As Rental_PromoCode, Special.DiscountPer As Rental_DiscountPer, (SELECT TOP 1 MinNights FROM Restriction WHERE Restriction.RentalId = Rental.id AND '3/10/2018 00:00:00' BETWEEN DateStarts AND DateEnds) As Rental_MinNights, (SELECT TOP 1 LOWER(CAST(CAST(Media.id AS VARCHAR) + ISNULL('-' + CAST(Media.MediaUrl AS VARCHAR(200)), '') + '.' + MediaType.MediaType AS VARCHAR(200))) FROM Media INNER JOIN MediaType ON Media.MediaTypeId = MediaType.id INNER JOIN RentalMedia ON Media.id = RentalMedia.MediaId WHERE Media.MediaTypeId BETWEEN 1 AND 9 AND RentalMedia.RentalId = Rental.id AND Media.Position = 1 ORDER BY Media.Position) AS Rental_ImageUrl_1, (SELECT TOP 1 LOWER(CAST(CAST(Media.id AS VARCHAR) + ISNULL('-' + CAST(Media.MediaUrl AS VARCHAR(200)), '') + '.' + MediaType.MediaType AS VARCHAR(200))) FROM Media INNER JOIN MediaType ON Media.MediaTypeId = MediaType.id INNER JOIN RentalMedia ON Media.id = RentalMedia.MediaId WHERE Media.MediaTypeId BETWEEN 1 AND 9 AND RentalMedia.RentalId = Rental.id AND Media.Position = 2 ORDER BY Media.Position) AS Rental_ImageUrl_2, (SELECT TOP 1 LOWER(CAST(CAST(Media.id AS VARCHAR) + ISNULL('-' + CAST(Media.MediaUrl AS VARCHAR(200)), '') + '.' + MediaType.MediaType AS VARCHAR(200))) FROM Media INNER JOIN MediaType ON Media.MediaTypeId = MediaType.id INNER JOIN RentalMedia ON Media.id = RentalMedia.MediaId WHERE Media.MediaTypeId BETWEEN 1 AND 9 AND RentalMedia.RentalId = Rental.id AND Media.Position = 3 ORDER BY Media.Position) AS Rental_ImageUrl_3, (SELECT TOP 1 LOWER(CAST(CAST(Media.id AS VARCHAR) + ISNULL('-' + CAST(Media.MediaUrl AS VARCHAR(200)), '') + '.' + MediaType.MediaType AS VARCHAR(200))) FROM Media INNER JOIN MediaType ON Media.MediaTypeId = MediaType.id INNER JOIN RentalMedia ON Media.id = RentalMedia.MediaId WHERE Media.MediaTypeId BETWEEN 1 AND 9 AND RentalMedia.RentalId = Rental.id AND Media.Position = 4 ORDER BY Media.Position) AS Rental_ImageUrl_4, (SELECT TOP 1 LOWER(CAST(CAST(Media.id AS VARCHAR) + ISNULL('-' + CAST(Media.MediaUrl AS VARCHAR(200)), '') + '.' + MediaType.MediaType AS VARCHAR(200))) FROM Media INNER JOIN MediaType ON Media.MediaTypeId = MediaType.id INNER JOIN RentalMedia ON Media.id = RentalMedia.MediaId WHERE Media.MediaTypeId BETWEEN 1 AND 9 AND RentalMedia.RentalId = Rental.id AND Media.Position = 5 ORDER BY Media.Position) AS Rental_ImageUrl_5, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 638) AS Rental_Feature_0, (SELECT TOP 1 Feature FROM Feature WHERE Id = 638) AS Rental_FeatureName_0, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 703) AS Rental_Feature_1, (SELECT TOP 1 Feature FROM Feature WHERE Id = 703) AS Rental_FeatureName_1, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 739) AS Rental_Feature_2, (SELECT TOP 1 Feature FROM Feature WHERE Id = 739) AS Rental_FeatureName_2, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 691) AS Rental_Feature_3, (SELECT TOP 1 Feature FROM Feature WHERE Id = 691) AS Rental_FeatureName_3, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 713) AS Rental_Feature_4, (SELECT TOP 1 Feature FROM Feature WHERE Id = 713) AS Rental_FeatureName_4, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 1842) AS Rental_Feature_5, (SELECT TOP 1 Feature FROM Feature WHERE Id = 1842) AS Rental_FeatureName_5, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 1840) AS Rental_Feature_6, (SELECT TOP 1 Feature FROM Feature WHERE Id = 1840) AS Rental_FeatureName_6, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 848) AS Rental_Feature_7, (SELECT TOP 1 Feature FROM Feature WHERE Id = 848) AS Rental_FeatureName_7, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 967) AS Rental_Feature_8, (SELECT TOP 1 Feature FROM Feature WHERE Id = 967) AS Rental_FeatureName_8, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 1845) AS Rental_Feature_9, (SELECT TOP 1 Feature FROM Feature WHERE Id = 1845) AS Rental_FeatureName_9, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 648) AS Rental_Feature_10, (SELECT TOP 1 Feature FROM Feature WHERE Id = 648) AS Rental_FeatureName_10, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 725) AS Rental_Feature_11, (SELECT TOP 1 Feature FROM Feature WHERE Id = 725) AS Rental_FeatureName_11, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 757) AS Rental_Feature_12, (SELECT TOP 1 Feature FROM Feature WHERE Id = 757) AS Rental_FeatureName_12, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 720) AS Rental_Feature_13, (SELECT TOP 1 Feature FROM Feature WHERE Id = 720) AS Rental_FeatureName_13, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 727) AS Rental_Feature_14, (SELECT TOP 1 Feature FROM Feature WHERE Id = 727) AS Rental_FeatureName_14, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 697) AS Rental_Feature_15, (SELECT TOP 1 Feature FROM Feature WHERE Id = 697) AS Rental_FeatureName_15, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 786) AS Rental_Feature_16, (SELECT TOP 1 Feature FROM Feature WHERE Id = 786) AS Rental_FeatureName_16, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 1844) AS Rental_Feature_17, (SELECT TOP 1 Feature FROM Feature WHERE Id = 1844) AS Rental_FeatureName_17, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 787) AS Rental_Feature_18, (SELECT TOP 1 Feature FROM Feature WHERE Id = 787) AS Rental_FeatureName_18, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 709) AS Rental_Feature_19, (SELECT TOP 1 Feature FROM Feature WHERE Id = 709) AS Rental_FeatureName_19, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 742) AS Rental_Feature_20, (SELECT TOP 1 Feature FROM Feature WHERE Id = 742) AS Rental_FeatureName_20, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 735) AS Rental_Feature_21, (SELECT TOP 1 Feature FROM Feature WHERE Id = 735) AS Rental_FeatureName_21, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 723) AS Rental_Feature_22, (SELECT TOP 1 Feature FROM Feature WHERE Id = 723) AS Rental_FeatureName_22, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 700) AS Rental_Feature_23, (SELECT TOP 1 Feature FROM Feature WHERE Id = 700) AS Rental_FeatureName_23, STUFF((SELECT ',' + CONVERT(VARCHAR(10), DateArrival, 101) + '-' + CONVERT(VARCHAR(10), DateDeparture, 101) FROM RentalBlock WHERE RentalId = Rental.id AND ((DateArrival BETWEEN '3/8/2018 00:00:00' AND '3/17/2018 23:59:59') OR (DateDeparture BETWEEN '3/8/2018 00:00:00' AND '3/17/2018 23:59:59')) ORDER BY DateArrival, DateDeparture FOR XML PATH('')), 1, 1, '') AS Rental_BlockedDates FROM Rental INNER JOIN Address On Rental.AddressId = Address.id INNER JOIN Content On Rental.ContentId = Content.id INNER JOIN Customer On Rental.CustomerId = Customer.id INNER JOIN RentalType On Rental.RentalTypeId = RentalType.id LEFT OUTER JOIN AddressType On Address.AddressTypeId = AddressType.id LEFT OUTER JOIN Software On Rental.SoftwareId = Software.id LEFT OUTER JOIN Staff On Rental.StaffId = Staff.id LEFT OUTER JOIN Contact On Staff.ContactId = Contact.id LEFT OUTER JOIN (SELECT MIN(Rate.MinRate) As MinNight, Rate.RentalId FROM Rate WHERE PastFlag = 0 AND RateTypeId = 1 AND '3/10/2018 00:00:00' BETWEEN DateStarts AND DateEnds GROUP BY Rate.RentalId) As MinRateNight On MinRateNight.RentalId = Rental.id LEFT OUTER JOIN (SELECT AVG(Rate.MinRate) As AvgNight, Rate.RentalId FROM Rate WHERE PastFlag = 0 AND RateTypeId = 1 AND '3/10/2018 00:00:00' BETWEEN DateStarts AND DateEnds GROUP BY Rate.RentalId) As AvgRateNight On AvgRateNight.RentalId = Rental.id LEFT OUTER JOIN (SELECT MIN(Rate.MinRate) As MinWeek, Rate.RentalId FROM Rate WHERE PastFlag = 0 AND RateTypeId = 2 AND '3/10/2018 00:00:00' BETWEEN DateStarts AND DateEnds GROUP BY Rate.RentalId) As MinRateWeek On MinRateWeek.RentalId = Rental.id LEFT OUTER JOIN (SELECT MIN(Rate.MinRate) As MinMonth, Rate.RentalId FROM Rate WHERE PastFlag = 0 AND RateTypeId = 3 AND '3/10/2018 00:00:00' BETWEEN DateStarts AND DateEnds GROUP BY Rate.RentalId) As MinRateMonth On MinRateMonth.RentalId = Rental.id LEFT OUTER JOIN (SELECT TOP 1 Special.Code, Special.DiscountPer, RentalGroup.RentalId FROM Special INNER JOIN WebPage On Special.WebPageId = WebPage.id INNER JOIN RentalGroup On WebPage.id = RentalGroup.WebPageId WHERE WebPage.WebPageTypeId = 10 AND WebPage.HiddenFlag = 0 AND getDate() BETWEEN WebPage.DateStarts AND WebPage.DateEnds AND '3/10/2018 00:00:00' BETWEEN WebPage.DateStarts AND WebPage.DateEnds ORDER BY WebPage.DateStarts DESC) As Special ON Special.RentalId = Rental.id WHERE Rental.id IS NOT NULL AND Rental.CustomerId IN (1000) AND Rental.RentalTypeId IN (1) AND Rental.Bedrooms IN (0) AND Rental.Sleeps IN (0) AND Rental.EnabledFlag = 1 AND (NOT EXISTS(SELECT * FROM RentalBlock WHERE RentalId = Rental.id AND '3/10/2018 23:59:59' < CONVERT(datetime, DateDeparture) AND '3/17/2018 00:00:00' > DateArrival) AND (EXISTS(SELECT * FROM Restriction WHERE RentalId = Rental.id AND MinNights <= 7 AND '3/10/2018 00:00:00' BETWEEN DateStarts AND DateEnds) OR NOT EXISTS(SELECT * FROM Restriction WHERE RentalId = Rental.id AND '3/10/2018 00:00:00' BETWEEN DateStarts AND DateEnds)) AND (EXISTS(SELECT * FROM TurnDay WHERE RentalId = Rental.id AND TurnDay IN (6) AND '3/10/2018 00:00:00' BETWEEN DateStarts AND DateEnds) OR NOT EXISTS(SELECT * FROM TurnDay WHERE RentalId = Rental.id AND '3/10/2018 00:00:00' BETWEEN DateStarts AND DateEnds))) ORDER BY CAST(AskingPrice.Price AS INT),Rental_Priority DESC,Rental_DateDisplayed