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 INNER JOIN product_categories_links ON ( (product_categories_links.PRODUCT_ID = p1.ID) AND (product_categories_links.Product_CATEGORIES_ID in (2002))) 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 = 100001 order by p.ProductSetDisplayOrder) as [ID]) AND ((PML.MembershipType = '100024') OR (PUL.USER_ID = 0)) and p1.productsize in (100001,100002) GROUP BY p1.id, p1.ProductTitle, p1.CardLayout, p1.IsOwnDesign, p1.ProductSize, p1.ProductSet, p1.Page_Number,PML.DisplayOrder, PUL.DisplayOrder, PFD.StartDate, PFD.EndDate, PFD.DisplayOrder, p1.DisplayOrder, p1.SyncID 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 6:21:10 PM
Christmas