0

I have a table with millions of rows in it that require that date spans with a certain "contract number" and "service code" not overlap. There are typically 2-4 rows with dates that overlap that must be combined into a single record. Records which do not overlap should not be combined, and gaps should be maintained.

ContractNumber     ServiceCode      StartDate      EndDate
1111111            AFL              2010-01-01     2010-12-31
1111111            AFL              2011-01-01     2011-12-31
1111111            AFL              2011-04-01     2012-03-31
1111111            AFL              2011-04-01     2012-06-30
1111111            AFL              2013-01-01     2013-12-31

Those must be combined into:

ContractNumber     ServiceCode      StartDate      EndDate
1111111            AFL              2010-01-01     2010-12-31   (remains the same)
1111111            AFL              2011-01-01     2012-06-30   (merged)
1111111            AFL              2013-01-01     2013-12-31   (gap maintained)

Also, I have already looked at this question: Eliminate and reduce overlapping date ranges. It worked under a subset of data, but it was really slow once I unleashed it on all the data that I have. I need something that runs in a few minutes for millions of rows. Any ideas?

Community
  • 1
  • 1
Russell Patterson
  • 622
  • 3
  • 10
  • 23

1 Answers1

0

Having spent much time on such problems, I think it is more efficient to keep the data clean, without overlaps, than to eliminate them from a large table. Constraints allow us to do it efficiently: Contiguous Time Periods

A-K
  • 16,804
  • 8
  • 54
  • 74
  • Your suggestion doesn't really solve my problem. I obviously know that cleaning the data is the best option, but business requirements prevent me from doing that. – Russell Patterson Aug 10 '11 at 19:42
  • @rwponu: I wish you good luck, but to my best knowledge there is no fast and easy solution. Adam Machanic has recommended CLR in such cases, but he said that he needed to write lot of complex C# code to solve this problem. – A-K Aug 11 '11 at 03:21