if exists (select * from sysobjects where id = object_id('dbo.si_sp_fiscal') and sysstat & 0xf = 4)
	drop procedure dbo.si_sp_fiscal
GO

/****************************************************************************/
-- Store Procedure:	si_sp_fiscal
-- Date:		3/15/02
-- Description:		This stored procedure is used as a function to
--			determine the fiscal info of a passed date.
--			You pass it a date and it returns the fiscal
--			year, fiscal period, and date ranges of period.
-- Outputs:		@fy_year = fiscal year
--			@fy_period = period number
--			@mtd_start = first day of period
--			@mtd_end = last day of period
--			@fy_yr_start = first day of year
--			@fy_yr_end = last day of year
-- Modified:		2/21/02
--			Added two more outputs for year.
/****************************************************************************/
CREATE PROCEDURE si_sp_fiscal
	@date smalldatetime,
	@fy_year smallint output,
	@fy_period smallint output,
	@wtd_start smalldatetime output,
	@wtd_end smalldatetime output,
	@mtd_start smalldatetime output,
	@mtd_end smalldatetime output,
	@ytd_start smalldatetime output,
	@ytd_end smalldatetime output
AS

/****************************************************************************/
-- Declare variables
/****************************************************************************/
-- Starting fiscal offsets
declare @fy_ofs_date smalldatetime
declare @fy_ofs_year smallint
declare @fy_ofs_period smallint

-- Variable for loop
declare @dloop smalldatetime

-- Variables to hold calculated values
declare @period smallint
declare @year smallint
declare @end smalldatetime

/****************************************************************************/
-- Initialize variables
/****************************************************************************/
-- Set the beginning offsets for FY 2002.  We will count forward or
--  backward from this point to get our information.
select @fy_ofs_date = '9/30/01'	-- Our fiscal year 2002 starts on this date
select @fy_ofs_year = 2002
select @fy_ofs_period = 1

/****************************************************************************/
-- Calculate fy values.  If the passed date is greater than or equal to our
-- FY date offset (the first known FY) then we increment up until we reach
-- our target date.  If it is less than our passed date, then we decrement
-- until we reach our target date.  At the time our loop ends, our counters
-- should contain the information we are looking for.
/****************************************************************************/
-- Is this date in the past or future?
if datediff(d,@fy_ofs_date,@date) >= 0
	begin
	-- Start at the offset and work up until we reach our date
	select @dloop = @fy_ofs_date
	select @period = 1
	select @year = @fy_ofs_year
	-- Loop through dates
	while @dloop <= @date
		begin
		-- Break out of loop if the next increment will
		--  put us over the selected date
		if @period = 3 or @period = 6 or @period = 9 or @period = 12
			begin
			if dateadd(d,35,@dloop) > @date
				begin
				break
				end
			end
		else
			begin
			if dateadd(d,27,@dloop) >= @date
				begin
				break
				end
			end
		-- Increment according to period
		if @period = 3 or @period = 6 or @period = 9 or @period = 12
			begin
			select @dloop = dateadd(d,35,@dloop)
			end
		else
			begin
			select @dloop = dateadd(d,27,@dloop)+1
			end
		-- Increment period number
		if @period = 12
			begin
			select @period = 1
			select @year = @year + 1
			end
		else
			begin
			select @period = @period + 1
			end
		end
	end
else
	begin
	-- Start at the offset and work down until we reach our date
	select @dloop = dateadd(d,-35,@fy_ofs_date)
	select @period = 12
	select @year = @fy_ofs_year - 1
	-- Loop through dates
	while @dloop > @date
		begin
		-- Break out of loop if the next decrement will
		--  put us past the selected date
		-- Decrement according to period
		if @period = 1 or @period = 4 or @period = 7 or @period = 10
			begin
			select @dloop = dateadd(d,-35,@dloop)
			end
		else
			begin
			select @dloop = dateadd(d,-29,@dloop)+1
			end
		-- Decrement period number
		if @period = 1
			begin
			select @period = 12
			select @year = @year - 1
			end
		else
			begin
			select @period = @period - 1
			end
		end
	end

-- Put results in output parameters
select @fy_year = @year
select @fy_period = @period
select @mtd_start = @dloop

-- Set last day of period
if @period = 3 or @period = 6 or @period = 9 or @period = 12
	begin
	select @mtd_end = dateadd(d,34,@mtd_start)
	end
else
	begin
	select @mtd_end = dateadd(d,27,@mtd_start)
	end

-- Set week to date range (loop backwards to find start of week - Sunday)
select @dloop = @date
while datepart(dw,@dloop) <> 1
	begin
	select @dloop = dateadd(d,-1,@dloop)
	end
select @wtd_start = @dloop
select @wtd_end = dateadd(d,6,@wtd_start)

-- Set fy year start and end dates
select @ytd_start = dateadd(wk,(@fy_year - @fy_ofs_year) * 52,@fy_ofs_date)
select @ytd_end = dateadd(d,-1,dateadd(wk,52,@ytd_start))