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 (
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


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s