Dynamic date roll up

Database

Dynamic date roll up

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 

2 REPLIES

Re: Dynamic date roll up

Hi someone managed to provide the answer.

select id,date, days_between, ceil(days_between/28) + 1 as rn

from

(

select id,date, date - min(date) over (partition by id) as days_between

from tablea

group by 1,2

) a ORDER BY 1,2

Doesn't being back the date, but does allow me to group the dates together to then select the earliest.

Senior Apprentice

Re: Dynamic date roll up

This will return a date based on your definition:

MIN(date) OVER (PARTITION BY id) + ((date - MIN(date) OVER (PARTITION BY id)) / 28 * 28)