All Forums Database
ScottDufour 4 posts Joined 06/12
11 Dec 2014
Overlapping time periods, competing records by date created.

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.
 
TABLE                                                          
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
 
Query output                                      
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.

dnoeth 4628 posts Joined 11/04
11 Dec 2014

This seems to be a similar problem to this on StackOverflow:
Create Historical Table from Dates with Ranked Contracts (Gaps and Islands?)
You just don't need to do the +/-1 because your implementation matches the period logic.

Dieter

ScottDufour 4 posts Joined 06/12
15 Dec 2014

Thanks, Dieter, this works very well.  There's no way I could have come up with this on my own.  Perfect.

You must sign in to leave a comment.