I have to check for minimum date each time and add 30 days to it and set the field accordingly.
For Example:
My Table:
ID StartDate EndDate
1 2017-01-01 2017-02-01
1 2017-01-09 2017-01-28
1 2017-04-01 2017-04-30
1 2017-04-05 2017-05-20
1 2017-04-20 2017-06-12
2 2017-06-02 2017-06-20
2 2017-06-14 2017-07-31
2 2017-06-14 2017-07-31
2 2017-06-19 2017-07-31
2 2017-06-19 2017-07-31
so, here min(startdate) is 2017-01-01. Now I add 30 days to it and my look up date is 2017-01-31. for any record which has start date less than this look up date I have to set flag_1.
My table will look like:
ID StartDate EndDate flag
1 2017-01-01 2017-02-01 flag_1
1 2017-01-09 2017-01-28 flag_1
1 2017-04-01 2017-04-30 null
1 2017-04-05 2017-05-20 null
1 2017-04-20 2017-06-12 null
2 2017-06-02 2017-06-20 null
2 2017-06-14 2017-07-31 null
2 2017-06-14 2017-07-31 null
2 2017-06-19 2017-07-31 null
2 2017-06-19 2017-07-31 null
Now I have to look for next minimum startdate which is greater than previous look up date(2017-01-31).so my min start date would be 2017-04-01. Now I add 30 days to it and my new look up date is 2017-05-01. For all the records less than new look up start date 2017-05-01 and greater than previous look up date(2017-01-31) the flag will be flag_2 and this goes on for same ID till it reaches the last record for same ID.
My final table should look like:
ID StartDate EndDate flag
1 2017-01-01 2017-02-01 flag_1
1 2017-01-09 2017-01-28 flag_1
1 2017-04-01 2017-04-30 flag_2
1 2017-04-05 2017-05-20 flag_2
1 2017-04-20 2017-06-12 flag_2
2 2017-06-02 2017-06-20 flag_1
2 2017-06-14 2017-07-31 flag_1
2 2017-06-14 2017-07-31 flag_1
2 2017-06-19 2017-07-31 flag_1
2 2017-06-19 2017-07-31 flag_1
i can get the first set of records by below code but unable to think of having this in a loop and having flag updated dynamically .
select a.*,'flag_1' as flag from table a
join
(select cast(min(startdate) as date) as minstartdate,cast(dateadd(day,30,min(startdate)) as date) as maxstartdate,ID from table
group by ID) adate
on cast(adate.maxstartdate as date)> cast(a.startdate as date)
and adate.id=a.id
where a.id=1
order by startdate
Can anyone help me with this logic?