SQL Query - creating procedures & using variables: SOLVED

[Vogel515]

This will be the first procedure I have ever written but pretty much this is what I need to do:

Query against a date dimension table to return the current fiscal year

Then create 8 additional variables which will use the current fiscal year variable to determine the start and end dates of the current fiscal year as well as the three proceeding fiscal years

ie:

declare var1 as smalldatetime

set var1 = '2/1/' + @Cur_FY
set var1 = '1/31/' + (@Cur_FY+1)
set var1 = '2/1/' + (@Cur_FY+1)
set var1 = '1/31/' + (@Cur_FY+2)
etc...

This information will then be used to create a query which will look at proposals with projected and awarded data, the goal is to use it to project funding going forward... I have no problem writing that query, its just figuring out what I'm doing wrong while working with the date values.

Thanks in advance for any help!

 

[KB]

There has got to be a better way. Can you store the current fiscal year in a table? Then link the proposal to its fiscal year? You can then group by this for summation queries.

 

[Vogel515]

I am trying to write this procedure so it does not need to be editing or ask for input going forward... I have a date dimension table which allows me to create joins to find the fiscal period/quarter/year... to get the current fiscal year I am doing this:

select
@Cur_FY = dd.fiscal_year
from
dbo.date_dimension as dd
where
dd.calendar_date = DATEADD(DD,DATEDIFF(DD,'1/1/1753',GetDate()),'1/1/1753')

Then I've got a large case statement which looks for overlapping periods for each fiscal year going forward, because not all proposals perfectly overlap we need to know what % of funding can be attributed to a given fiscal year. (This is where I need those variables)

 

[KLin]

I"m confused. What's the actual problem?

 

[Vogel515]

I apologize for not being clear... the problem is now solved

1. This procedure will be run on demand and I wanted it to be able to figure out the current year...

declare @Cur_FY as int
select @Cur_FY = dd.FY
from date_dimension as dd
dd.date = dateadd(dd,datediff(dd,'1/1/1753',Getdate(),'1/1/1753') -- gets current date

2. Then I needed to give values to a number of variables, all incremental values of the current fiscal year (@Cur_FY)

set @FY00s = convert(datetime,'10/1'+case((@Cur_FY - 1) as varchar(5)),101)
set @FY00e = convert(datetime,'9/30'+case((@Cur_FY) as varchar(5)),101)
set @FY01s = convert(datetime,'10/1'+case((@Cur_FY) as varchar(5)),101)
set @FY01e = convert(datetime,'10/1'+case((@Cur_FY + 1) as varchar(5)),101)
etc...

3. Then I needed to compare date ranges of proposals against fiscal years

CASE WHEN @FY01s >= start_date THEN
CASE WHEN @FY03e >= end_date THEN
CASE WHEN @FY03s >= end_date THEN 0
ELSE DATEDIFF(dd,@FY03s,end_date)
END
ELSE DATEDIFF(dd,@FY00s,@FY00e)
END
ELSE CASE WHEN @FY00e <= end_date THEN
CASE WHEN start_date >= @FY00e THEN 0
ELSE DATEDIFF(dd,start_date,@FY00e)
END
ELSE DATEDIFF(dd,end_date,start_date)
END
END as 'FY +00'

This provided me with the number of days a project proposal overlapped a given fiscal year.

Thanks again for the attempt at the help, sorry I was not clear. Working with date ranges was not as easy as I thought it'd be.