2 Replies Latest reply: Mar 5, 2014 9:32 AM by Michael Distasio RSS

    Merge Continuous Intervals

      So I'm looking to merge a few intervals into a single table in QV, and I was wondering if anyone had any ideas.

       

      Right now I have a simple table that breaks out intervals, however, a new interval is created every year if it crosses the calendar year.

       

      I need to figure out to merge these back into single intervals.  Does anyone have suggestions on where to start?

       

      Ex:

       

      I have a table with the following structure.

       

      IDStart

      End

      11/1/20103/7/2010
      11/1/20115/31/2011
      110/30/201112/31/2011
      11/1/201212/31/2012
      11/1/20133/31/2013
      15/13/201312/31/2013
      11/1/201412/31/2014

       

      I'm looking to get the following:

       

      IDStartEnd
      11/1/20103/7/2010
      11/1/20115/31/2011
      110/31/20113/13/2013
      15/13/201312/31/2014

       

       

      I have some rough ideas how to get there, but they all involve some fairly large joins and I was hoping to avoid that since the source table is several million records.