LucidDbAppLib FISCAL TIME DIMENSION

From Eigenpedia

Jump to: navigation, search

Contents

Syntax

FROM TABLE( APPLIB.FISCAL_TIME_DIMENSION ( 
  start_year, start_month, start_day, end_year, end_month, end_day,
  fiscal_start_month ) )

FISCAL_TIME_DIMENSION is a UDX, which must be invoked in the FROM clause of a SQL query.

Purpose

Generates a cached table with special, time-related columns for all the days in a specified time period.

The following are the columns generated in the cached table:

  • TIME_KEY_SEQ [INTEGER]
  • TIME_KEY [DATE]
  • DAY_OF_WEEK [VARCHAR(10)]
  • WEEKEND [VARCHAR(1)]
  • DAY_NUMBER_IN_WEEK [INTEGER]
  • DAY_NUMBER_IN_MONTH [INTEGER]
  • DAY_NUMBER_IN_QUARTER [INTEGER]
  • DAY_NUMBER_IN_YEAR [INTEGER]
  • DAY_NUMBER_OVERALL [INTEGER]
  • DAY_FROM_JULIAN [INTEGER] -- NOTE: we do not calculate Julian days (JD) per the astronomical standard; they do not start noon Universal Time (UT) Monday, January 1, 4713 BC. So Julian days may not be in line with Julian days supplied by other systems. Also, Julian days for BC dates are not currently supported.
  • WEEK_NUMBER_IN_MONTH [INTEGER]
  • WEEK_NUMBER_IN_QUARTER [INTEGER]
  • WEEK_NUMBER_IN_YEAR [INTEGER]
  • WEEK_NUMBER_OVERALL [INTEGER]
  • MONTH_NAME [VARCHAR(10)]
  • MONTH_NUMBER_IN_QUARTER [INTEGER]
  • MONTH_NUMBER_IN_YEAR [INTEGER]
  • MONTH_NUMBER_OVERALL [INTEGER]
  • QUARTER [INTEGER]
  • YR [INTEGER]
  • CALENDAR_QUARTER [VARCHAR(6)]
  • WEEK_START_DATE [DATE]
  • WEEK_END_DATE [DATE]
  • MONTH_START_DATE [DATE]
  • MONTH_END_DATE [DATE]
  • QUARTER_START_DATE [DATE]
  • QUARTER_END_DATE [DATE]
  • YEAR_START_DATE [DATE]
  • YEAR_END_DATE [DATE]
  • FISCAL_YEAR [INTEGER]
  • FISCAL_DAY_NUMBER_IN_QUARTER [INTEGER]
  • FISCAL_DAY_NUMBER_IN_YEAR [INTEGER]
  • FISCAL_WEEK_START_DATE [DATE]
  • FISCAL_WEEK_END_DATE [DATE]
  • FISCAL_WEEK_NUMBER_IN_MONTH [INTEGER]
  • FISCAL_WEEK_NUMBER_IN_QUARTER [INTEGER]
  • FISCAL_WEEK_NUMBER_IN_YEAR [INTEGER]
  • FISCAL_MONTH_START_DATE [DATE]
  • FISCAL_MONTH_END_DATE [DATE]
  • FISCAL_MONTH_NUMBER_IN_QUARTER [INTEGER]
  • FISCAL_MONTH_NUMBER_IN_YEAR [INTEGER]
  • FISCAL_QUARTER_START_DATE [DATE]
  • FISCAL_QUARTER_END_DATE [DATE]
  • FISCAL_QUARTER_NUMBER_IN_YEAR [INTEGER]
  • FISCAL_YEAR_START_DATE [DATE]
  • FISCAL_YEAR_END_DATE [DATE]

Note: FISCAL_TIME_DIMENSION interprets calendar parameters in a lenient mode. This means dates such as as 02/29/1997 will be converted automatically to a correct date which does exist, 03/01/1997. For more info on calendar leniency see the 'java.util.Calendar javadoc'.

Parameters

  • start_year: The year of the start date of the table, must not be null [INTEGER]
  • start_month: The month of the start date of the table, must not be null [INTEGER]
  • start_day: The day of the start date of the table, must not be null [INTEGER]
  • end_year: The year of the end date of the table, must not be null [INTEGER]
  • end_month: The month of the end date of the table, must not be null [INTEGER]
  • end_day: The day of the end date of the table, must not be null [INTEGER]
  • fiscal_start_month: The starting month of the fiscal year, must not be null [INTEGER]

Example

SELECT TIME_KEY_SEQ, TIME_KEY, DAY_OF_WEEK, WEEKEND, DAY_NUMBER_IN_WEEK
FROM TABLE( APPLIB.FISCAL_TIME_DIMENSION( 
  1998, 12, 28, 1999, 1, 2, 7) )

RETURNS:

+---------------+-------------+--------------+----------+----------------------+
| TIME_KEY_SEQ  |  TIME_KEY   | DAY_OF_WEEK  | WEEKEND  | DAY_NUMBER_IN_WEEK   |
+---------------+-------------+--------------+----------+----------------------+
| 1             | 1998-12-28  | Monday       | N        | 2                    |
| 2             | 1998-12-29  | Tuesday      | N        | 3                    |
| 3             | 1998-12-30  | Wednesday    | N        | 4                    |
| 4             | 1998-12-31  | Thursday     | N        | 5                    |
| 5             | 1999-01-01  | Friday       | N        | 6                    |
| 6             | 1999-01-02  | Saturday     | Y        | 7                    |
+---------------+-------------+--------------+----------+----------------------+

Source Code

http://p4web.eigenbase.org/open/dev/luciddb/src/com/lucidera/luciddb/applib/datetime/FiscalTimeDimensionUdx.java

Personal tools