SQL: SELECT TOP 100 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) As Rental_TurnDay, Special.Code As Rental_PromoCode, Special.DiscountPer As Rental_DiscountPer, (SELECT TOP 1 MinNights FROM Restriction WHERE Restriction.RentalId = Rental.id) 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 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 33311) AS Rental_Feature_0, (SELECT TOP 1 Feature FROM Feature WHERE Id = 33311) AS Rental_FeatureName_0, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 33312) AS Rental_Feature_1, (SELECT TOP 1 Feature FROM Feature WHERE Id = 33312) AS Rental_FeatureName_1, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 33493) AS Rental_Feature_2, (SELECT TOP 1 Feature FROM Feature WHERE Id = 33493) AS Rental_FeatureName_2, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 33466) AS Rental_Feature_3, (SELECT TOP 1 Feature FROM Feature WHERE Id = 33466) AS Rental_FeatureName_3, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 33313) AS Rental_Feature_4, (SELECT TOP 1 Feature FROM Feature WHERE Id = 33313) AS Rental_FeatureName_4, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 33477) AS Rental_Feature_5, (SELECT TOP 1 Feature FROM Feature WHERE Id = 33477) AS Rental_FeatureName_5, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 33476) AS Rental_Feature_6, (SELECT TOP 1 Feature FROM Feature WHERE Id = 33476) AS Rental_FeatureName_6, (SELECT TOP 1 FeatureValue FROM RentalFeature WHERE RentalId = Rental.id AND FeatureId = 33375) AS Rental_Feature_7, (SELECT TOP 1 Feature FROM Feature WHERE Id = 33375) AS Rental_FeatureName_7 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 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 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 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 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 ORDER BY WebPage.DateStarts DESC) As Special ON Special.RentalId = Rental.id LEFT OUTER JOIN (SELECT MIN(RentalFeature.FeatureValue) AS Price, RentalFeature.RentalId FROM RentalFeature INNER JOIN Feature ON RentalFeature.FeatureId = Feature.Id WHERE Feature.Feature = 'Asking Price' GROUP BY RentalFeature.RentalId) AS AskingPrice ON AskingPrice.RentalId = Rental.Id WHERE Rental.id IS NOT NULL AND Rental.CustomerId IN (1000) AND Rental.RentalTypeId IN (4) AND Rental.EnabledFlag = 1 AND Rental.Bedrooms BETWEEN 0 AND 99 AND (SELECT MIN(RentalFeature.FeatureValue) FROM RentalFeature INNER JOIN Feature ON RentalFeature.FeatureId = Feature.Id WHERE Feature.Feature = 'Asking Price' AND RentalId = Rental.id) BETWEEN 0 AND 10000000 ORDER BY Rental_DateCreated DESC DESC,Rental_Id DESC,Rental_Priority DESC,Rental_DateDisplayed