SQLTeam.com | Weblogs | Forums

Year on year values on rules

sql2012

#1

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  

#2

Output number 1:

Select
	YEARS.id,
	DATES.marketdate
From
(select distinct id, year(marketdate) as y from @tbl) YEARS
Join
(
Select 
	id, 
	year(max(marketdate)) as y, 
	iif(max(marketdate) = EOMONTH(max(marketdate)), EOMONTH(max(marketdate)), null) as marketdate
From @tbl 
Group By id, year(marketdate)
) DATES On YEARS.id = DATES.id And YEARS.y = DATES.y
Order By YEARS.id, YEARS.y Desc

Then just tweak the IIF and we have Output 2

Select
	YEARS.id,
	DATES.marketdate
From
(select distinct id, year(marketdate) as y from @tbl) YEARS
Join
(
Select 
	id, 
	year(max(marketdate)) as y, 
	iif(max(marketdate) = EOMONTH(max(marketdate)), EOMONTH(max(marketdate)), max(marketdate)) as marketdate
From @tbl 
Group By id, year(marketdate)
) DATES On YEARS.id = DATES.id And YEARS.y = DATES.y
Order By YEARS.id, YEARS.y Desc