clear markdown compare help donate comments/suggestions/bugs a leap of faith? diddy dollings
clear markdown donate comments/suggestions/bugs a leap of faith?
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 432148 distinct fiddles created so far.

create table test ( id int , gendate1 date , gendate2 date , gendate3 date , gendate4 date , gendate5 date , gendate6 date , gendate7 date , gendate8 date )
 hidden batch(es)


insert into test values (1,'1/6/2019','4/6/2019','2/6/2019','3/6/2019','5/6/2019','7/6/2019','8/6/2019','10/6/2019'), (2,'1/5/2019','4/5/2019','2/5/2019','3/5/2019','2/1/2019','7/5/2019','2/1/2019','10/5/2019')
2 rows affected
 hidden batch(es)


with cte1 as ( select id, 1 num, gendate1 gendate from test union all select id, 2 num, gendate2 gendate from test union all select id, 3 num, gendate3 gendate from test union all select id, 4 num, gendate4 gendate from test union all select id, 5 num, gendate5 gendate from test union all select id, 6 num, gendate6 gendate from test union all select id, 7 num, gendate7 gendate from test union all select id, 8 num, gendate8 gendate from test ), cte2 as ( select id, num, gendate, rank() over (partition by id order by gendate desc) rnk from cte1 where gendate <= '1/30/2019' union all select id, num, gendate, rank() over (partition by id order by gendate asc) rnk from cte1 where gendate >= '1/30/2019' ) select id, max(num) num, gendate from cte2 where rnk = 1 group by id, gendate order by id, gendate
id num gendate
1 1 06/01/2019 00:00:00
1 3 06/02/2019 00:00:00
2 1 05/01/2019 00:00:00
2 7 01/02/2019 00:00:00
 hidden batch(es)