|
Saving a
more general definition for later, let's say that every week starts on Sunday and ends the following Saturday. The fiscal year begins on the Sunday closest to September 1, which is equivalent to the Sunday following August 28. Given a date and the date of the corresponding fiscal New Year's Day, computing the ISO week is just a matter of counting the days between
them and dividing by seven. If the resulting number (disregarding any fractional part) is n, the week is n+1. The trickiest part is determining what is "corresponding fiscal New Year's Day," because, as you pointed out, September 1 may be in week one of the new year, or it may be in week 52 or 53 of the old year. (The same goes for all the dates between August 29 and September 3.)
The function fiscal_week, below, returns the value you're looking for.
CREATE OR REPLACE FUNCTION fiscal_new_year
-- *************************************
-- ** f i s c a l _ n e w _ y e a r **
-- *************************************
-- fiscal_new_year returns the date of the
-- start of the most recent fiscal year on
-- or before in_dt.
-- FUNCTION fiscal_new_year
(
in_dt IN DATE
)
RETURN DATE
DETERMINISTIC
AS
fny_dt DATE; -- candidate for being returned
year_text CHAR (4) := TO_CHAR (in_dt, 'YYYY');
BEGIN
-- fny may be in the same calendar year or the previous one
FOR try_year IN 1 .. 2
LOOP
-- fny is the first Sunday after August 28
fny_dt := NEXT_DAY ( TO_DATE ( year_text || '0828',
'YYYYMMDD'
),
'SUNDAY' -- First day of the week
);
IF fny_dt <= in_dt
THEN -- This guess was correct
EXIT;
END IF;
-- in_dt is before fny_dt; get set to try previous year
year_text := TRIM ( TO_CHAR ( TO_NUMBER ( year_text,
'9999'
) - 1,
'9999'
)
);
END LOOP;
RETURN fny_dt;
END fiscal_new_year;
/
CREATE OR REPLACE FUNCTION fiscal_week
-- *****************************
-- ** f i s c a l _ w e e k **
-- *****************************
-- fiscal_week returns an integer (1-53) indicating
-- which week of the fiscal year in_dt is in.
-- FUNCTION fiscal_week
(
in_dt IN DATE
)
RETURN PLS_INTEGER
DETERMINISTIC
AS
BEGIN
RETURN 1 + FLOOR ((in_dt - fiscal_new_year (in_dt)) / 7);
END fiscal_week;
/
Continued in part 2.
|
