What would you like to know more about?

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