Expiring Option Prices
This View displays Product Option Prices for upcoming Events that have an expiration (Days Out to Hide):
Difficulty: Advanced
Purpose: To track expiring Product Option Prices.
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