sql - Keep maximum date every 30 days starting from a beginning date -

i trying keep max date every 30 days based on starting date. simplicity's sake, tested on 1 id, actual data contains many ids.

my data looks this:

id     date      15jan2016      11mar2016      13may2016      25may2016      26may2016      08jun2016      09jun2016      10jun2016 

the end table should this:

id      max_srv_dt_1    max_srv_dt_2   max_srv_dt_3  max_srv_dt_4       15jan2016       11mar2016      13may2016     10jun2016 

my code:

 select c1.id,          min_srv_dt_1,          max_srv_dt_2,          max(c2.date) on (partition c2.id order c2.date+30) max_srv_dt_3      (select c1.id, min_srv_dt_1, max(c2.date) on (partition c2.id order c2.date+30) max_srv_dt_2                   (select c1.id, min(c1.date) min_srv_dt_1            max_ep_test c1            group c1.id) c1        left outer join max_ep_test c2            on c1.id = c2.id         , c2.date < c1.min_srv_dt_1 + 30       ) c1       left outer join max_ep_test c2         on c1.id = c2.id        , c2.date > max_srv_dt_2 + 30 

i following, , can't figure out how change parameters take correct dates:

id   min_srv_dt_1   max_srv_dt_2   max_srv_dt_3    15jan2016      15jan2016      11mar2016    15jan2016      15jan2016      13mar2016    15jan2016      15jan2016      13may2016    15jan2016      15jan2016      25may2016    15jan2016      15jan2016      26mary016    15jan2016      15jan2016      08jun2016    15jan2016      15jan2016      09jun2016    15jan2016      15jan2016      10jun2016 

you can calculate difference between date , earliest date using datediff(). rest aggregation:

select id, max(date) (select et.*,              datediff('day', min(date) on (partition id), date) datediff_day       max_ep_test et      ) et group floor(datediff_day / 30); 

wiki