sql - Overlapping Spans -

i trying write query reorders date ranges around particular spans. should looks this

row   rank   begin date   end date 1     b      3/24/13      11/1/13 2          10/30/13     4/9/15 3     b      3/26/15      12/31/15 

and have become

row   rank   begin date   end date 1     b      3/24/13      10/29/13 2          10/30/13     4/9/15 3     b      4/10/15      12/31/15 

to explain further, dates in row 2 ranked higher (a>b), dates in row 1 , 3 have change around dates in row 2 in order avoid overlap in dates.

i using sql server 2008 r2

you can use following query:

;with cte (    select row, rank, begindate, enddate,           row_number() on (order begindate) rn    mytable ), toupdate (    select c1.row, c1.rank, c1.begindate, c1.enddate,           c2.rank prank, c2.enddate penddate,            c3.rank nrank, c3.begindate nbegindate    cte c1    left join cte c2 on c1.rn = c2.rn + 1    left join cte c3 on c1.rn = c3.rn - 1    c1.rank = 'b' ) update toupdate set begindate = case                     when penddate null                        begindate                    when (penddate >= begindate) , (prank = 'a')                        dateadd(d, 1, penddate)                    else begindate                 end,     enddate = case                     when nbegindate null                        enddate                    when (nbegindate <= enddate) , (nrank = 'a')                        dateadd(d, -1, nbegindate)                    else enddate                 end  

a cte constructed assign consecutive, ascending numbers every record of table. row_number() window function used purpose.

using cte basis construct toupdate. latter cte contains date values of current previous , next records. left join:

left join cte c2 on c1.rn = c2.rn + 1 

is used join previous record, whereas one:

left join cte c3 on c1.rn = c3.rn - 1 

is used join next record.

using case expressions can identify overlaps, and, in case there one, perform update.

demo here