SELECT p1.ID, p1.ProductTitle, p1.CardLayout, p1.IsOwnDesign, p1.ProductSize, p1.ProductSet, p1.Page_Number, p1.SyncID, IsNull(PFD.DisplayOrder, null) FeaturedDisplayOrder, IsNull(PML.DisplayOrder, null) MembershipDisplayOrder, IsNull(PUL.DisplayOrder, null) UserDisplayOrder FROM [products] p1 outer apply(select TOP 1 * from [products_membership_links] AS PML where p1.ID = PML.ProductID and PML.MembershipType = '100024') AS PML outer apply(select TOP 1 * from [products_user_links] AS PUL where p1.ID = PUL.ProductID and PUL.USER_ID = 0) AS PUL outer apply(select TOP 1 * from [products_featured_dates] AS PFD where p1.ID = PFD.ProductID) AS PFD LEFT OUTER JOIN products_featured_dates AS PFD2 ON p1.id = PFD2.ProductID WHERE p1.id in (SELECT (select top 1 p.id from products p inner join productsize on p.productsize = productsize.ID where p.ProductSet = p1.ProductSet and p.PAGE_NUMBER = 1 and p.FLAG_ACTIVE = 1 and p.productType in (100024, 100016, 100017) order by p.ProductSetDisplayOrder) as [ID]) AND ((PML.MembershipType = '100024') OR (PUL.USER_ID = 0)) and p1.productsize in (100012,100013,100029) AND (IsNull(PFD2.HideAfterFeature, 0) = 0 OR (PFD2.HideAfterFeature = 1 and( ( PFD2.EndDate < CONVERT(DATE, '1-1-1901') AND CONVERT(DATE, GETDATE()) BETWEEN CONVERT(DATE, DATEADD(YEAR, DATEPART(YEAR, GETDATE()) - DATEPART(YEAR, PFD2.StartDate), PFD2.StartDate)) AND CONVERT(DATE, DATEADD(YEAR, DATEPART(YEAR, GETDATE()) - DATEPART(YEAR, PFD2.EndDate), PFD2.EndDate)) ) OR ( PFD2.EndDate >= CONVERT(DATE, '1-1-1901') AND ( CONVERT(DATE, GETDATE()) <= CONVERT(Date, DATEADD(YEAR, DATEPART(YEAR, GETDATE()) - DATEPART(YEAR, PFD2.EndDate), PFD2.EndDate)) OR CONVERT(DATE, GETDATE()) BETWEEN CONVERT(DATE, DATEADD(YEAR, DATEPART(YEAR, GETDATE()) - DATEPART(YEAR, PFD2.StartDate), PFD2.StartDate)) AND CONVERT(DATE, DATEADD(YEAR, DATEPART(YEAR, GETDATE()) - DATEPART(YEAR, CONVERT(DATE, '12-31-1900')), PFD2.EndDate)) ) ) ) ) ) ORDER BY IsNull(PUL.DisplayOrder, 0), case when ( (PFD.EndDate < CONVERT(DATE, '1-1-1901') AND CONVERT(DATE, GETDATE()) BETWEEN CONVERT(Date, DATEADD(YEAR, DATEPART(YEAR, GETDATE()) - DATEPART(YEAR, PFD.StartDate), PFD.StartDate)) AND CONVERT(DateTime, DATEADD(YEAR, DATEPART(YEAR, GETDATE()) - DATEPART(YEAR, PFD.EndDate), PFD.EndDate)) ) OR (PFD.EndDate >= CONVERT(DATE, '1-1-1901') AND ( CONVERT(DATE, GETDATE()) <= CONVERT(DATE, DATEADD(YEAR, DATEPART(YEAR, GETDATE()) - DATEPART(YEAR, PFD.EndDate), PFD.EndDate)) OR CONVERT(DATE, GETDATE()) BETWEEN CONVERT(DATE, DATEADD(YEAR, DATEPART(YEAR, GETDATE()) - DATEPART(YEAR, PFD.StartDate), PFD.StartDate)) AND CONVERT(DATE, DATEADD(YEAR, DATEPART(YEAR, GETDATE()) - DATEPART(YEAR, CONVERT(DATE, '12-31-1900')), PFD.EndDate)) ) ) ) then IsNull(PFD.DisplayOrder, 0) else IsNull(PML.DisplayOrder, 0) end;--sql bug fix6/3/2026 5:30:50 PM