0

I have a table with multiple Street Names with Street Number ranges and date ranges. The problem is that there are some overlapping not only in dates, but in street numbers too, and I need to split it. I saw a question about how to split it by Dates (SQL Server separate overlapping dates), but my problem seems to be slightly different but much more complex.

For Example, this is how it looks right now:

StartDate   EndDate     StreetName      StreetNumberLow     StreetNumberHigh        Value 
2017/1/1    2017/3/31   MyStreet        5                   7                       1729 
2017/4/1    2020/12/31  MyStreet        5                   7                       1763
2017/1/1    2017/12/31  MyStreet        1                   11                      1729
2018/1/1    2020/12/31  MyStreet        1                   11                      18128

How it should look:

StartDate   EndDate     StreetName      StreetNumberLow     StreetNumberHigh        Value
2017/1/1    2017/3/31   MyStreet        5                   7                       1729
2017/4/1    2020/12/31  MyStreet        5                   7                       1763
2017/1/1    2017/12/31  MyStreet        1                   4                       1729
2017/1/1    2017/12/31  MyStreet        8                   11                      1729
2018/1/1    2020/12/31  MyStreet        1                   4                       18128
2018/1/1    2020/12/31  MyStreet        8                   11                      18128

As you can see, there was two overlaps in Street Number and Dates, so I need to split it. Can someone please help me on finding the solution to this? Thank you

EDITED: Another case example: If I have Street Number range 1 - 11 from January to June with Value 100, and Street Number range 5 - 7 from March to December with Value 200, then the result should be as follows: Street Number range 1 - 11 from January to February with Value 100, Street Number ranges 1 - 4 with Value 100, 5 - 7 with Value 200 and 8 - 11 with Value 100 from March to June, and Street Number range 5 - 7 from July to December with Value 200

Federico Navarro
  • 61
  • 8
  • 2
    Can you Please give more details. I am not sure what you mean by Split them?. where does the New High and Low number come from. What are you trying to split exactly? – John Feb 02 '18 at 14:51
  • as it overlaps in addresses 1 to 11, in the same dates, I need to split the address range into 1 - 4, 5 - 7 and 8 - 11 – Federico Navarro Feb 02 '18 at 14:54
  • 1
    Can you elaborate on the conditions of overlap? from the looks of the result you are saying row 3 and row 1 overlap, but it is not clear what the conditions of splitting are because the dates between row 1 and 3 are different – Henry Lu Feb 02 '18 at 14:55
  • Row 1 and 3 overlaps, but doesn't really matters, because they have the same Value. In the other hand, rows 2 and 3 overlaps, and that does matters, because if I look for the value of 5 in July 2017, I have two possible values for it: 1763 and 1729, and that is the problem. – Federico Navarro Feb 02 '18 at 15:19
  • Hi, can you please update your question's sample data with the 1763 and 1729 values that you are referring to. Also, how does the dates interfer with "overlap" what if only a portion of the date overlaps, would that then spawn another row just to cover the dates it doesn't overlap? – Henry Lu Feb 02 '18 at 15:43
  • The sample data is with 1763 and 1729 examples. I'll add the case you mentioned to the example, so let me know if that makes sense to you. Thanks! – Federico Navarro Feb 02 '18 at 15:53
  • @HenryLu I added the example in the EDITED section. Thanks – Federico Navarro Feb 02 '18 at 16:03
  • I still unable to understand what you are trying to accomplish, Sorry – John Feb 02 '18 at 21:14

0 Answers0