I have a table with id and date I need two outputs as below. I will pass an input date and I need records according to below conditions
output 1: I need less than or equal date data either it is month end or not for the first row and first-row date-1 year month end data and first-row date-2 year month end data and if data is not available for the particular year then return null on that row.
output 2: Same as first but with a twist that I need whatever data is available on first row date-1 and -2 I do not need null.
declare @tbl table (id int , marketdate date ) insert into @tbl (id,marketdate) values (1,'2018-05-31'), (1,'2017-05-29'), (1,'2016-05-31'), (2,'2018-02-28'), (2,'2017-02-28'), (2,'2016-02-29'), (2,'2016-02-28')
My query :
;with cte as ( select id , marketdate from ( select id , marketdate ,row_number() over(partition by id order by marketdate desc) rn from @tbl where marketdate <='2018-06-05' ) a where rn=1 union all select id , marketdate from ( select b.id , b.marketdate ,row_number() over(partition by b.id order by b.marketdate desc) rn from @tbl b inner join cte c on b.id= c.id where b.marketdate<= dateadd(year,-1,c.marketdate ) ) b where rn=1 ) select * from cte order by id, marketdate desc
1 2018-05-31 1 NUll 1 2016-05-31 2 2018-02-28 2 2017-02-28 2 2016-02-29
1 2018-05-31 1 2017-05-29 1 2016-05-31 2 2018-02-28 2 2017-02-28 2 2016-02-29