Hello, I hope I'm going to explain this well enough for everyone to understand the problem.
Currently I have IDs with dates assoicated with it. What I would like to do is take the earliest date and group everyone within 28 days (allocate them the same date). For dates that fall out of this range, I want to do the same again. Using the earliest date, group all dates within 28 days and continue doing until everything is grouped.
The dates will variey based upon ID, so won't all begining with 2014/01/01
Highlighed below is the additional coloumn I would like.
Thank you in advance!
ID Date NEW_DATE
1 2014/01/01 2014/01/01
1 2014/01/15 2014/01/01
1 2014/01/27 2014/01/01
1 2014/01/29 2014/01/29
1 2014/02/03 2014/01/29
Hi someone managed to provide the answer.
This will return a date based on your definition:
MIN(date) OVER (PARTITION BY id) + ((date - MIN(date) OVER (PARTITION BY id)) / 28 * 28)