Solved

# Find Fiscal Year date falls between.

Posted on 2007-10-04
Medium Priority
370 Views
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 cant 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
0
Question by:fleet
• 2

LVL 16

Expert Comment

ID: 20017311
select rate from table where CONVERT(VARCHAR, @start_date, 101) between '7/1/2007' to '6/31/2008'
0

Author Comment

ID: 20017440
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

LVL 19

Accepted Solution

folderol earned 2000 total points
ID: 20018382

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

Author Comment

ID: 20022217
It worked great.  Thanks so much! You saved me  a lot of time.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
###### Suggested Courses
Course of the Month16 days, 11 hours left to enroll