如何循环访问表以查找下一个最小日期

Question

我必须找出一个成员的最短开始日期，并为每个成员执行以下步骤

+-----------+-----------+-----------+
| memberid  | startdate |  enddate  |
+-----------+-----------+-----------+
| 200000000 | 1/3/2017  | 1/17/2017 |
| 200000000 | 1/3/2017  | 1/9/2017  |
| 200000000 | 1/10/2017 | 1/24/2017 |
| 200000000 | 1/18/2017 | 1/31/2017 |
| 200000000 | 1/18/2017 | 1/24/2017 |
| 200000000 | 1/25/2017 | 2/8/2017  |
| 200000000 | 2/17/2017 | 2/19/2017 |
| 200000000 | 2/21/2017 | 3/7/2017  |
| 200000000 | 2/21/2017 | 2/28/2017 |
| 200000000 | 3/1/2017  | 3/30/2017 |
| 200000000 | 3/3/2017  | 4/1/2017  |
| 200000000 | 3/31/2017 | 4/26/2017 |
| 200000000 | 4/27/2017 | 5/1/2017  |
| 200000000 | 5/2/2017  | 5/6/2017  |
| 200000000 | 5/7/2017  | 5/31/2017 |
| 200000000 | 5/11/2017 | 6/1/2017  |
| 200000000 | 6/5/2017  | 6/16/2017 |
| 200000000 | 6/17/2017 | 6/30/2017 |
+-----------+-----------+-----------+
1.in this example, my first minimum(startdate)= 1/3/2017  . I add 30 days to it and calculated enddate is 2/2/2017. My first 30 days window is from 1/3/2017-2/2/2017. for each member this span becomes initial_1. this 30 days window is calcualted and look up table kind.
2. Similarly my second range is calcualted from second minimum start date. here my second minimum start date becomes 2/17/2017 because 1/3/2017-1/25/2017 belongs to first window range. so second 30 days range is from 
2/17/2017+30 days =3/19/2017. my second window range is from 2/17/2017-3/19/2017 . 
Third range will be from 3/31/2017(this is the next minimum start date)-4/30/2017. this is how my 30 days span is calculated. 
in each span, if servicedate <=14 days, flag it is reinitial_1 else, if servicedate > 15 days then flag it as initial_1. so in this example, my first initial ended on enddate 2/2/2017 and second started on 2/17/2017 which is >15 days from 2/2/2017 so it is flagged as initial_1. 
if date is less than 14 days then it should be flagged as reinitial.

我的最终输出应该是这样的

+-----------+-----------+-----------+-----------+
| memberid  | startdate |  enddate  |   flag    |
+-----------+-----------+-----------+-----------+
| 200000000 | 1/3/2017  | 1/17/2017 | Initial_1 |
| 200000000 | 1/3/2017  | 1/9/2017  | Initial_1 |
| 200000000 | 1/10/2017 | 1/24/2017 | Initial_1 |
| 200000000 | 1/18/2017 | 1/31/2017 | Initial_1 |
| 200000000 | 1/18/2017 | 1/24/2017 | Initial_1 |
| 200000000 | 1/25/2017 | 2/8/2017  | Initial_1 |
| 200000000 | 2/17/2017 | 2/19/2017 | Initial_1 |
| 200000000 | 2/21/2017 | 3/7/2017  | Initial_1 |
| 200000000 | 2/21/2017 | 2/28/2017 | Initial_1 |
| 200000000 | 3/1/2017  | 3/30/2017 | Initial_1 |
| 200000000 | 3/3/2017  | 4/1/2017  | Initial_1 |
| 200000000 | 3/31/2017 | 4/26/2017 | ReInitial |
| 200000000 | 4/27/2017 | 5/1/2017  | ReInitial |
| 200000000 | 5/2/2017  | 5/6/2017  | ReInitial |
| 200000000 | 5/7/2017  | 5/31/2017 | ReInitial |
| 200000000 | 5/11/2017 | 6/1/2017  | ReInitial |
| 200000000 | 6/5/2017  | 6/16/2017 | ReInitial |
| 200000000 | 6/17/2017 | 6/30/2017 | ReInitial |
+-----------+-----------+-----------+-----------+

我从下面的逻辑开始，但这没有正确地给我最小(开始日期(。

select
memberid
,startdate
,dateadd(dd,30,min(startdate))
from #t  
group by
memberid
,startdate
order by
memberid
,startdate

这有点复杂的逻辑，我不确定我是否可以解释清楚。 谁能帮我这个逻辑？

Answer 1

如果我做对

了

SELECT memberid, startdate, enddate
,flag = CASE WHEN diff <=30 THEN 'Initial_1' 
ELSE CASE WHEN diff % 31 <=14 THEN 'ReInitial' 
ELSE 'Initial_1' END END
FROM (    
SELECT memberid, startdate, enddate, diff = datediff(day, min(startdate) over(partition by memberid), startdate)
FROM myTable 
) t