将每个值循环到 seq num [英] Loop through each value to the seq num

问题描述

我有以下数据，想通过将 29 天添加到开始日期来为每个 ID 创建序列号.

I have below data and want to create sequence number for each ID by adding 29 days to Start Date.

即对于 ID 1，查找将是 seq num 1 的最小值(开始日期)然后添加 29.这将是 Seq Num 2，然后继续直到我们遍历每个 Auth ID.每次查找每个 ID 的最大值(结束日期).当此循环达到该值时，为每个值停止它.

i.e. for ID 1, look for minimum(start date) that will be seq num 1 and then add 29. this will be Seq Num 2 and then go on till we loop through each Auth ID. each time look for max(end date) for each ID. when this loop reaches that value stop it for each value.

ID  StartDate   EndDate
1   2017-01-01  2017-01-15
1   2017-01-15  2017-02-14
2   2017-01-01  2017-01-15
2   2017-01-05  2017-03-05
2   2017-01-10  2017-02-04
3   2017-01-01  2017-01-15
3   2017-01-16  2017-01-16
3   2017-01-17  2017-01-21
3   2017-01-22  2017-02-13
3   2017-02-14  2017-03-21
3   2017-02-16  2017-03-21
4   2017-01-01  2017-01-15
4   2017-01-16  2017-02-16
4   2017-01-19  2017-02-16
4   2017-02-17  2017-03-17
4   2017-03-18  2017-03-30
4   2017-03-22  2017-03-30
4   2017-03-31  2017-04-28

Expected OutPut 
ID  SeqNum     StartDate     EndDate      New Date
1     1        2017-01-01   2017-01-15    2017-01-30
1     2        2017-01-15   2017-02-14    2017-02-28 (2017-02-28 is > than max(end date) for this ID so pick the next ID)
2     1        2017-01-01   2017-01-15    2017-01-30
2     2        2017-01-05   2017-03-05    2017-02-28
2     3        2017-01-10   2017-02-04    2017-03-29 (2017-03-29 is > than max(end date-2017-03-05) for this ID so pick the next ID)

基本上，我需要遍历每个 ID，为服务日期添加 29 天，直到达到最大结束日期并放置序列号.对所有 ID 重复此操作.

Basically, I need to loop through every ID add 29 days for Service date till it reaches max end date and put the seq num. Repeat this for all ID's.

我尝试通过 CTE 添加 29 天，但未能将其与 ID 的结束日期进行比较.

i tried doing CTE to add the 29 days but failing to compare it with end date for an ID.

有人可以帮我吗?

推荐答案

你是说这个吗?

with myData as
(
select ID,
row_Number() over (partition by Id order by id, StartDate) as SeqNum,
min(startdate) over (partition by Id) as minDate,
startDate, endDate
from myTable
)
select id, seqNum, startDate, endDate, dateadd(day, seqNum*29, minDate) as newDate
from myData;

或者这个:

with myData as
(
select ID,
row_Number() over (partition by Id order by id, StartDate) as SeqNum,
min(startdate) over (partition by Id) as minDate, 
max(endDate) over (partition by Id)as maxDate,
startDate, endDate
from myTable
)
select id, seqNum, startDate, endDate, 
 case 
 when maxDate < dateadd(day, seqNum*29, minDate)
 then maxDate 
 else dateadd(day, seqNum*29, minDate) end as newDate
from myData;

这篇关于将每个值循环到 seq num的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！