sql server - Nesting case statements -

i'm trying fathom out query when today falls within financial year (1/11 - 31/10), run set of case statements, when today in next financial year run next set etc...

i've tried nesting case statements not working. below explain trying do..

thanks looking!

if getdate() between '2012-11-01 00:00' , '2013-10-31 23:59' -- year?          case          when b.saledate between '2011-11-01 00:00' , getdate()-365 'yes'          when b.saledate between '2012-11-01 00:00' , getdate() 'yes'         else 'no'         'financial year date',         end  if getdate() between '2013-11-01 00:00' , '2014-10-31 23:59' -- year yet?         case          when b.created between '2011-11-01 00:00' , getdate()-730 'yes'          when b.created between '2012-11-01 00:00' , getdate()-365 'yes'          when b.created between '2013-11-01 00:00' , getdate() 'yes'         else 'no'         'financial year date',         end  end 

this way end ever increasing code.

to fiscal year oyu may try that:

declare @datetocheck datetime  set @datetocheck = '20130101'  select year(dateadd(mm,-10,@datetocheck)) 

so if have table transactions datetime field transactiondate

select year(dateadd(mm,-10,transactiondate))   transactions 

will fiscal year of each transaction.