This View displays Product Option Prices for upcoming Events that have an expiration (Days Out to Hide):
Page: Product Option Prices
This view shows all the Product Option Prices for future Events which have a value set for Days Out to Hide.
The following SQL is added to the list of fields:
Product_Option_Group_ID_Table_Product_ID_Table.[Product_Name] , Product_Option_Prices.[Option_Title] , Product_Option_Prices.[Days_Out_To_Hide] AS [Days Out To Hide] , (SELECT TOP 1 Ev.Event_Title FROM Events Ev WHERE Ev.Online_Registration_Product = Product_Option_Group_ID_Table.Product_ID AND Ev.Event_Start_Date > GETDATE() ORDER BY Ev.Event_Start_Date) AS [Event] , (SELECT TOP 1 Ev.Event_Start_Date FROM Events Ev WHERE Ev.Online_Registration_Product = Product_Option_Group_ID_Table.Product_ID AND Ev.Event_Start_Date > GETDATE() ORDER BY Ev.Event_Start_Date) AS [Event Start Time] , (SELECT TOP 1 DATEADD(dd,-Product_Option_Prices.[Days_Out_To_Hide],CONVERT(date,Ev.Event_Start_Date)) FROM Events Ev WHERE Ev.Online_Registration_Product = Product_Option_Group_ID_Table.Product_ID AND Ev.Event_Start_Date > GETDATE() ORDER BY Ev.Event_Start_Date) AS [Hide Date] , (SELECT TOP 1 DATEDIFF(dd,GETDATE(),Ev.Event_Start_Date) FROM Events Ev WHERE Ev.Online_Registration_Product = Product_Option_Group_ID_Table.Product_ID AND Ev.Event_Start_Date > GETDATE() ORDER BY Ev.Event_Start_Date) AS [Days Till Event]
This criteria limits the result to only active Product Option Prices having an expiration (Days Out to Hide) which is also associated with a future Event:
Product_Option_Prices.[Days_Out_To_Hide] IS NOT NULL AND Product_Option_Prices.Active = 1 AND EXISTS (SELECT TOP 1 1 FROM Events Ev WHERE Ev.Online_Registration_Product = Product_Option_Group_ID_Table.Product_ID AND Ev.Event_Start_Date > GETDATE())
This View uses the following SQL Functions: