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.

Field List

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]

Criteria

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())

Techniques

This View uses the following SQL Functions:

• DATEADD
• DATEDIFF
• EXISTS
• GETDATE