Overlapping time periods, competing records by date created.

Database

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.

2 REPLIES
Junior Contributor

Re: Overlapping time periods, competing records by date created.

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.

Re: Overlapping time periods, competing records by date created.

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