Effective Price

Effective Price

Code Example:  (SQL Server / MySQL / Oracle) SQL Server: 1. 1: CREATE View v_EffectivePrice 2: As 3: Select a.EffectiveDate as StartDate , 4: ( 5: Select DateAdd(day,-1,ISNULL(Min(b.EffectiveDate), DateAdd( 6: year,50,GETDATE() ) ) ) 7: From EffectivePrice b Where b.ProductID=a.ProductID And b.EffectiveDate > 8: a.EffectiveDate 9: ) As EndDate, a.ProductID, a.EffectivePrice 10: From EffectivePrice As a 11: 2. Use the view to get price on any date /time 12: Select ep.ProductID, ep.Price from v_EffectivePrice as ep 13: Where @mydate 14: Between ep.StartDate And ep.EndDate And ep.ProductID=@myProductID

Explanation Use case: Store web-fronts need this capability. Requirement: You want the price to be effective from certain date/time and you want to set it up in advance. As for example, you want a promotional price to be effective in a certain date/time . Table Design: EffectivePrice ——————– ProductID int EffectiveDate DateTime Price              Money Primary Key (ProductID, EffectiveDate) Technology Highlight: Self Join