Find Fiscal Year date falls between.

I have a MS SQL stored procedure that is supposed to find a rate in a table called class by the class code (@Class_code) and start date (@start_date).

To do this I need to determine the fiscal year the @start_date falls within and then find the Execution Date that is between the fiscal year.  Our fiscal year for example is 7/1/2007 to 6/31/2008.

I know how to do the select statement but cant figure to determine the fiscal year a date falls between. For example 7/31/2007 falls between the fiscal year 7/1/2007 to 6/31/2008.

Can anyone tell me what the code is to find the fiscal year?  My Boss wants this done ASAP.  


Class_code  Execution Date            Rate
TT                2007-08-01 00:00:01.000      .300
TT                2006-07-01 00:00:01.000      .240
TT                2005-07-01 00:00:01.000      .210
TT                2004-08-01 00:00:01.000      .220
TT                2003-08-01 00:00:01.000      .230
TT                2002-07-01 00:00:01.000      .230
TT                2001-07-01 00:00:01.000      .230
TT                1998-01-01 00:00:00.000      .170
fleetAsked:
Who is Participating?
 
folderolCommented:

create procedure findrate @startdate datetime, @classcode nvarchar(10)
as
declare @fystart datetime
declare @fyend datetime
declare @fy int

if cast('7/1/'+ datename(year,@startdate) as datetime) > @startdate
set @fy = 0
else
set @fy = 1
set @fystart = cast(cast(datepart(year,@startdate) -1 + @fy as varchar(4)) +'0701' as datetime)
Set @fyend   = cast(cast(datepart(year,@startdate) + @fy as varchar(4)) +'0630 23:59:59' as datetime)
select Rate from yourtable where class_code = @classcode and [Execution Date] between @fystart and @fyend

Tom
0
 
SQL_SERVER_DBACommented:
select rate from table where CONVERT(VARCHAR, @start_date, 101) between '7/1/2007' to '6/31/2008'
0
 
fleetAuthor Commented:
Your solultion will only work if every @start_date falls within fiscal year 7/1/2007 to 6/31/2008. The start dates may fall into a different fiscal years.  Such as @start_date = 6/31/2005.  So I need to figure out which fiscal year each @start_date fall in.
0
 
fleetAuthor Commented:
It worked great.  Thanks so much! You saved me  a lot of time.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.