I am trying to update a table of dates where for each id they overlap with the previous row. However, each update can cause the next row that did not overlap to now overlap. As an example:
| ID | Number_Days | Start_Date | End_Date |
| ------------ | ------------ | ------------ | ------------ |
| 1 | 30 | 12/13/2018 | 1/11/2019 |
| 1 | 30 | 12/28/2018 | 1/26/2019 |
| 1 | 30 | 12/31/2018 | 1/29/2019 |
should look like:
ID | Number_Days | Start_Date | End_Date | start_date_new | end_date_new |
---|---|---|---|---|---|
1 | 30 | 12/13/2018 | 1/11/2019 | 12/13/2018 | 1/11/2019 |
1 | 30 | 12/28/2018 | 1/26/2019 | 1/12/2019 | 2/11/2019 |
1 | 30 | 12/31/2018 | 1/29/2019 | 2/12/2019 | 3/13/2019 |
my code output looks like:
ID | Number_Days | Start_Date | End_Date | start_date_new | end_date_new |
---|---|---|---|---|---|
1 | 30 | 12/13/2018 | 1/11/2019 | 12/13/2018 | 1/11/2019 |
1 | 30 | 12/28/2018 | 1/26/2019 | 1/12/2019 | 2/11/2019 |
1 | 30 | 12/31/2018 | 1/29/2019 | 1/27/2019 | 2/26/2019 |
as you can see from my actual output - the code i'm using does create new column with updated dates based on previous row, but it updates the 3rd row based on the second row original start/end date as opposed to their new ones. the code I'm using is this. just not sure how to do it recursively until there is no overlap for each id, or what would be the best way to do so. for each id there are a variable number of rows.
select
*
, case
when start_date <= lag(end_date) over(partition by id order by start_date) then dateadd(1,1, lad(eng_date) over partition by id order by start_date))
else start_date
end as start_date_new
, dateadd(d, Number_Days, case when start_date <= lag(end_date) over(partition by id order by start_date) then dateadd(1,1, lad(eng_date) over partition by id order by start_date))
else start_date
end) as end_date_new
from
my_table
1条答案
按热度按时间bt1cpqcv1#
You could achieve this with recursive Common Table Expressions.
I've provided a working sample below. The first CTE cte_rownum adds a row number to each id to enable us to iterate in the next CTE cte_recur. Also, I've kept the option maxrecursion to 0 which is unlimited, you could set it another value depending on your data.
Let me know if it helps.