I have a very large dataset of multiple part number with different pricing for different time periods. Often all or a portion of a time period is superceded by a later record, and then returns to the original price after the superceding period ends. I need a querty that sorts all this out and gives me a result showing the correct price in discrete time periods. I've tried many different attempts, but they all get bulky and don't get the job done.
I suspect that TD 13.0's period functions would help, but I've really struggled for a method. I'd appreciate any help given.
Rec; PartNumber; CreateDate; PriceStart; PriceEnd; Price
A; A1234; 11/12/2008; 01/01/2009; 12/31/2009; 20.00
B; A1234; 04/01/2009; 01/01/2009; 12/31/2009; 21.00
C; A1234; 09/01/2009; 09/10/2009; 12/31/2009; 22.00
D; A1234; 11/20/2009; 01/01/2010; 12/31/2010; 27.00
E; A1234; 06/01/2010 06/02/2010; 06/30/2010; 25.00
Rec PartNumber; CreateDate; PriceStart; PriceEnd; Price
B; A1234; 04/01/2009; 01/01/2009; 09/10/2009; 21.00
C A1234; 09/01/2009; 09/10/2009; 12/31/2009; 22.00
D; A1234; 11/20/2009; 01/01/2010; 06/02/2010; 27.00
E; A1234; 06/01/2010; 06/02/2010; 06/30/2010; 25.00
D; A1234; 11/20/2009; 06/30/2010; 12/31/2010; 27.00
Note how Record A is not in the result, as it is eclipsed by record B that has a later CreateDate. Also Record D is briefly eclipsed by Record E, then returns after E expires.
There are multiple part numbers in the real table, but I've shown only one here.
This seems to be a similar problem to this on StackOverflow:
You just don't need to do the +/-1 because your implementation matches the period logic.