Date to Fiscal Week Oracle Function Musings (52/53 Week Retail Fiscal Calendar)

Create or Replace Function DT2FW

( bar IN Date )

Return Number

IS

numout Number;

/*******************************************************************************

version 0.1 # Date conversion to Fiscal Week Number # Jeff I # 2011-JUL-04

********************************************************************************

Input: date

Ouput: numeric Fiscal Week Number

********************************************************************************

52/53 Week Fiscal Year at Whole Foods Market IT Notes:

* Weeks end on Sunday and Year Ends on the last complete week in September

* Hence, the Fiscal Year ends on the last Sunday of September

* There are 13 Fiscal Periods, generally four weeks long

, weeks sequenced 1-2-3-4

* On a "leap" (53 week) year, the 13th period contains the "extra" week

, weeks sequenced 1-2-3-3-4

********************************************************************************

Code Notes:

To get the Leap Year FP13 week sequence 1-2-3-3-4:

* Use Mod 4 of the Input Date minus the Begining Date of the Fiscal Year

, decode the integer outputs [0-3] to week numbers [1-4]

, but offset the start of the year by "three weeks"

(the Sunday following (Input Date - 22))

, and offset the decode as well

*******************************************************************************/

bar_year Number := Extract( YEAR from bar );

Begin

If trunc( bar ) <= Next_Day( To_Date( bar_year || '-OCT-01', 'YYYY-MON-DD') - (1+7*3), 'SUN')

Then bar_year := bar_year - 1;

End If;

Select decode(

Mod(Floor((Trunc( bar ) - 1 - Next_Day( To_Date( To_Char( bar_year )||'-OCT-01', 'YYYY-MON-DD') - (1+7*3), 'SUN'))/7),4

),2,1,3,2,0,3,1,4,999) Into numout From Dual;

Return numout;

End DT2FW;

/

Alter Session Set NLS_Date_Format='YYYY-MON-DD';

Alter Session Set NLS_Date_Format='YYYY-MM-DD_HH24:MI';

Alter Session Set NLS_Date_Format='DD-MM-YY';

Select DT2FW('2012-OCT-01') FW From Dual;

Select DT2FW('01-10-12') FW From Dual;

/* Financials Fiscal Period Dates */

Select Accounting_Period, DT2FW(Begin_Dt) Week_Beg, DT2FW(End_Dt) Week_End, Begin_Dt, End_Dt From SYSADM.PS_Cal_Detp_Tbl@FSTST.wholefoods.com

Where SetID = 'WHOLE' And Calendar_ID = 'FP' And Fiscal_Year = 2012 Order By 1;

/* A Date Series */

Select ds, dt2fw(ds) fw

From (Select to_date('2012-JUL-01', 'YYYY-MON-DD') + sn as ds

From dual

Model

Dimension By (0 key)

Measures (0 sn)

Rules Iterate (111) (sn [ITERATION_NUMBER] = ITERATION_NUMBER ))

Order By 1;

Drop Function DT2FW

/

Oracle PL/SQL Function - Hybrid Kibibyte/Engineering Number Presentation

Create or Replace Function KiBEng

/* version 0.5 # Hybrid Kibibyte/Engineering Number Presentation # Jeff I # 2011-MAY-10 */

( bar IN Number )

Return VarChar2

IS

strout VarChar2(16);

Begin

If bar < 0 Then Raise_Application_Error(-20001,'Number Is Less Than Zero');

End If;

If bar is Null Then Raise_Application_Error(-20002,'Number Is NULL');

End If;

If bar = 0 Then strout := '0_B';

Else Select

To_Char(bar/Power(1024,Floor(Log(1024,bar))),'999.999') ||

Decode(Floor(Log(1024,bar)),0,'_B',1,'_Kib',2,'_MiB',3,'_GiB',4,'_TiB',4,'_PiB','-ERR') Into strout

From DUAL;

End If;

Return strout;

Exception

When Others Then Raise_Application_Error(-20000,'That Ain`t Right! '||SQLCODE||' - '||SQLERRM);

End KiBEng;

/

fallen tree on shoal creek

crepes at Tobin's

Maker_Faire

zax st_arnold october_fest beer

acl_2007 stephen_marley