Mark Last Five Fiscal Years in a Column


  

CREATE TABLE dbo.financial_years (id int identity(1,1),	start_of_fin_year date,	end_of_fin_year date);

-- Load sample financial_years from 2008 to 2017
	INSERT INTO dbo.financial_years (start_of_fin_year, end_of_fin_year)
	SELECT	DATEADD(YEAR, -1*n , start_of_fin_year),	DATEADD(YEAR, -1*n , end_of_fin_year)
	from (select '2018-04-01' as start_of_fin_year, '2019-03-31' as end_of_fin_year) t
			Cross apply(values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) d(n)
--get beginningofCurrentFY
;with mycte as (
select
Case when Month(getdate())>3 then Dateadd(month,3,dateadd(Year, datediff(year,0,getdate()),0))
else Dateadd(month,3,dateadd(Year, datediff(year,0,getdate())-1,0)) end beginningofCurrentFY
)

-- declare @today date ='2016-4-2'
-- ;with mycte as (select
--Case when Month( @today)>3 then Dateadd(month,3,dateadd(Year, datediff(year,0, @today),0))
--else Dateadd(month,3,dateadd(Year, datediff(year,0, @today)-1,0)) end beginningofCurrentFY)

select start_of_fin_year, end_of_fin_year ,
case when  start_of_fin_year<=beginningofCurrentFY
and  start_of_fin_year< (dateadd(Year,-5,beginningofCurrentFY) )
then 'Yes' else 'No' End    from financial_years
cross apply (Select beginningofCurrentFY from mycte) d(beginningofCurrentFY)

drop table financial_years

----https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3d640159-5247-4323-8cf9-63141571ca8e/dynamically-i-want-to-get-last-5-financial-years?forum=transactsql
 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s