[TYPO] "... starting with October [NOT September] ..." -Steve
-----Original Message-----
From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Wills,
Steve
Sent: Monday, February 25, 2008 10:26 AM
To: RBASE-L Mailing List
Subject: [RBASE-L] - RE: Fiscal Year/Period Conversion From Date (Table,
Data, & Stored Procedure)
Forgot to say that changing the Fiscal Calendar is as easy as UPDATEing
the data in the PERIODS table.
For Example: If the Fiscal Year begins in October, as opposed to July,
as the data in the table indicate, just re-number PERIODS.Period from
1-12, starting with September, rather than July. Then, set the value
for PERIODS.YearAdjustment to 0 for any month where the actual
CalendarYear=FiscalYear. For JUL-JUN FY's that's January-June. For
OCT-SEP FY's, October, November, and December shold have
PERIODS.YearAdjustment=1. Of course, PeriodText would require UPDATEing
also.
(NOTE: The actual order of the rows is irrelevant for processing. I
just started with July, since JUL-JUN is our FY. So, the bottom 2
examples (A) and (B), each for an OCT-SEP FY, function the same.)
-- JUL-JUN FISCAL YEAR Data
Period MonthName YearAdjustment MonthNumber PeriodText
MonthNumberText
------ ------------ ---------------- -------------- ------------
------------------
1 July 1 7 01 07
2 August 1 8 02 08
3 September 1 9 03 09
4 October 1 10 04 10
5 November 1 11 05 11
6 December 1 12 06 12
7 January 0 1 07 01
8 February 0 2 08 02
9 March 0 3 09 03
10 April 0 4 10 04
11 May 0 5 11 05
12 June 0 6 12 06
-- OCT-SEP FISCAL YEAR Data (A)
Period MonthName YearAdjustment MonthNumber PeriodText
MonthNumberText
------ ------------ ---------------- -------------- ------------
------------------
10 July 0 7 10 07
11 August 0 8 11 08
12 September 0 9 12 09
1 October 1 10 01 10
2 November 1 11 02 11
3 December 1 12 03 12
4 January 0 1 04 01
5 February 0 2 05 02
6 March 0 3 06 03
7 April 0 4 07 04
8 May 0 5 08 05
9 June 0 6 09 06
-- OCT-SEP FISCAL YEAR Data (B)
Period MonthName YearAdjustment MonthNumber PeriodText
MonthNumberText
------ ------------ ---------------- -------------- ------------
------------------
1 October 1 10 01 10
2 November 1 11 02 11
3 December 1 12 03 12
4 January 0 1 04 01
5 February 0 2 05 02
6 March 0 3 06 03
7 April 0 4 07 04
8 May 0 5 08 05
9 June 0 6 09 06
10 July 0 7 10 07
11 August 0 8 11 08
12 September 0 9 12 09
If this is useful to anyone, thanks,
Steve
-----Original Message-----
From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Wills,
Steve
Sent: Friday, February 22, 2008 5:21 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Fiscal Year/Period Conversion From Date (Table,
Data, & Stored Procedure)
I don't know how anyone handles/treats the conversion of a DATE-value to
some sort of corresponding FISCAL-value. However, I found a little
table/data-driven solution on WWW that looked pretty simple (and
elegant?). I also wrote a stored procedure (SP) to handle the
conversion. The SP approach can be superior to a correlated query if a
table contains several dates to be converted and those contain NULL
values, which would not correlate to the PERIODS table.
Anyway, the TABLE, Data, SP Code, and an example are below. It's a bit
to digest, but I think it's decent - useful to me, anyway. If it's is
useful to anyone else, cool, especially since I'm the one usually askin'
f/help from all y'all. Still, If anybody has additional tips/advice,
I'd appreciate it.
Also, tomorrow PM, y'all all tune-in to ESPN and scream for the Tigers,
'cause they R:GR8 ... I just hope they can do it.
Have a nice weekend,
Steve in Memphis
Table: PERIODS Cursor Lock(s)
Descr: Conversion Data for Date-to-Fiscal Values
No. Column Name Attributes
--- ------------------
------------------------------------------------------
1 Period Type : INTEGER NOT NULL
Consrnt: PRIMARY KEY
2 MonthName Type : TEXT 10
3 YearAdjustment Type : INTEGER
4 MonthNumber Type : INTEGER
5 PeriodText Type : TEXT 8
Comment: For Leading Zeroes
6 MonthNumberText Type : TEXT 8
Comment: For leading zeroes.
Current number of rows: 12
Period MonthName YearAdjustment MonthNumber PeriodText
MonthNumberText
------ ------------ ---------------- -------------- ------------
------------------
1 July 1 7 01 07
2 August 1 8 02 08
3 September 1 9 03 09
4 October 1 10 04 10
5 November 1 11 05 11
6 December 1 12 06 12
7 January 0 1 07 01
8 February 0 2 08 02
9 March 0 3 09 03
10 April 0 4 10 04
11 May 0 5 11 05
12 June 0 6 12 06
-- *********************************************************
-- *** SP_GET_FISCAL_DATE.RMD ******************************
-- *********************************************************
-- *** 2008.02.15, JSW *************************************
-- *** To receive a DATE-Value and Desired Return Format
-- *** Convert it using the PERIODS table,
-- *** then Return a STRING-Value in the desired format
-- *** Ex: CALL SP_GET_FISCAL_DATE('12/19/1960',CCYYPP)
-- *** : would return '196106'
-- *********************************************************
SET VAR vFCCYY TEXT =NULL
SET VAR vFPP TEXT =NULL
SET VAR vFReturnValue TEXT =NULL
IF (IFNULL(.vDate2Convert,0,1)) = 1 THEN
SELECT +
((IYR(.vDate2Convert)) + YearAdjustment), +
PeriodText +
INTO +
vFCCYY INDICATOR viFCY, +
vFPP INDICATOR viFP +
FROM +
PERIODS +
WHERE +
((IMON(.vDate2Convert)) = MonthNumber)
ELSE
-- Handle this NULL-value scenario
SET VAR vFormat='NO_DATE'
ENDIF
SWITCH (.vFormat)
CASE 'CCYY'
SET VAR vFReturnValue=.vFCCYY
BREAK
CASE 'PP'
SET VAR vFReturnValue=.vFPP
BREAK
CASE 'CCYYPP'
SET VAR vFReturnValue=(.vFCCYY + .vFPP)
BREAK
CASE 'NO_DATE'
SET VAR vFReturnValue='NO_DATE'
BREAK
DEFAULT
SET VAR vFReturnValue=.vFCCYY
BREAK
ENDSW
RETURN .vFReturnValue
RETURN
-- *** Statement to grab some records from the PROPOSAL table with a
couple of date COL's
-- *** NOTE 1: DATETIME-value converted to DATE-value within CALL() by
use of DEXTRACT()
-- *** NOTE 2: SP called in WHERE-clause operation, again converting
DATETIME with DEXTRACT()
-- *** NOTE 3: By our protocol, the first 2 digits of PROPOSAL_NUMBER
are FISCAL YY.
-- *** : Here, I am looking for mis-matches between the
FY-component in PROPOSAL_NUMBER
-- *** : what it should be based on the value in CREATE_TIMESTAMP.
-- *** NOTE 4: The CVAL('ROWCOUNT') is just a temp/virtual autonum
SELECT (CVAL('ROWCOUNT')),
PROPOSAL_NUMBER,
SEQUENCE_NUMBER,
CREATE_TIMESTAMP,(CALL
GetFiscalDate(DEXTRACT(CREATE_TIMESTAMP),'CCYY')),
REQUESTED_START_DATE_INITIAL, (CALL
GetFiscalDate(DEXTRACT(REQUESTED_START_DATE_INITIAL),'CCYY'))
FROM R_PROPOSAL
WHERE ('20' + (SGET(PROPOSAL_NUMBER,2,1)) ) <> (CALL
GetFiscalDate(DEXTRACT(CREATE_TIMESTAMP),'CCYY'))
J. Stephen Wills
Program Manager, Research Informatics
Office of the Vice Chancellor for Research
University of Tennessee Health Science Center
62 S. Dunlap, Suite 400
Memphis, TN 38163
Office: 901-448-2389
FAX : 901-448-7133
