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
output 1:
1 2018-05-31
1 NUll
1 2016-05-31
2 2018-02-28
2 2017-02-28
2 2016-02-29
output 2:
1 2018-05-31
1 2017-05-29
1 2016-05-31
2 2018-02-28
2 2017-02-28
2 2016-02-29