Changing the business calendar

Database
Enthusiast

Changing the business calendar

Hi What is the best way to change the sys_calendar.BusinessCalendar, mainly looking at changing the BusinessYearBegin to be 1st July and the BusinessYearEnd to be 30th June regardless of the day of the week (Australian financial year). Probably also want to change the start of the week to Monday like the ISO calendar, but this may be just by changing the session Calendar type to ISO. It may just be easier to build in the logic into a SQL UDF and have the users call that to calculate the start and end of the financial year. ,case when mydate <= Cast((extract(year from mydate) || '-07-01') as date) then Cast((extract(year from mydate)-1 || '-07-01') as date) else Cast((extract(year from mydate) || '-07-01') as date) end as StartFY ,case when mydate <= Cast((extract(year from mydate) || '-06-30') as date) then Cast((extract(year from mydate) || '-06-30') as date) else Cast((extract(year from mydate)+1 || '-06-30') as date) end as EndFY All users currently have the default session Calendar setting of Teradata. We are running TD 15.00.06.17 Cheers Steven
1 REPLY
Junior Contributor

Re: Changing the business calendar

The best way is to dump the crap.. awful calendars in sys_calendar altogether.

 

#1: Columns in a calendar are usually materialized to be able to collect statistics, otherwise the optimizer doesn't know how many days per week or months per year, etc. exist. 

Ok, in newer releases you can collect stats on calculations, too, but why to recalculate the fact that today 2017-11-05 is a Sunday, the fifth day of the month, week 44, etc. over and over again. A calendar table is always small, thus usually cached (I created a calendar once for the full range of the Date datatype, 9999 years are still only 3.6 million rows).

 

#2: The calculations used in the BusinessCalendar are done in a very complicated way, to put it mildly (simply Explain it).

 

#3: The ISO calendar returns months starting on Monday with 4 or 5 full weeks (either 28 or 35 days), It's also not resulting in a 4-4-5-week or whatever accounting period.) and an ISO year 364 or 371 days (52 or 53 weeks). This might be based on some strange interpretation of the ISO standard, but i doubt it's used anywhere. It's also not based on a 4-4-5-week or whatever accounting period.

 

Create your own calendar with physical columns, you will always need some extra calculations like (business) holidays, etc.

Populate all columns once, e.g. using a SELECT all you need plus more FROM sys_calendar.calendar.

Collect Stats on all columns.

 

As all business calendar columns also exist as UDFs you can utilize those to get the ISO week like WEEKNUMBER_OF_YEAR(cdate, 'ISO'). Be aware that they only work for the years 1900 to 2100 (don't ask why), if you want to extend your calendar you can't use them.

 

For a Financial year July to June you can utilize ROUND(cDate, 'y').

EXTRACT(YEAR FROM ROUND(cDate, 'y') = FY Number

ADD_MONTHS(ROUND(cDate, 'y'),-6) = Begin FY

ADD_MONTHS(ROUND(cDate, 'y'),+6)-1 = End FY

 

And then you probably need holidays based on Easter Sunday:

/***
Calculation of easter sunday, implemented using an algorithm from
http://www.merlyn.demon.co.uk/estralgs.txt

Based on easter sunday the moveable christian feasts can be calculated, e.g.:
Mardi Gras     - 47 days before easter sunday
Ascension Day  - 39 days after easter sunday
Pentecost      - 49 days after easter sunday
Corpus Christi - 60 days after easter sunday


SELECT easter_sunday(2013);

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.

easter_sunday(2013)
-------------------
         2013-03-31
***/

REPLACE FUNCTION easter_sunday(yr INTEGER)
RETURNS DATE
SPECIFIC easter_sunday_I
RETURNS NULL ON NULL INPUT
CONTAINS SQL
DETERMINISTIC
COLLATION INVOKER
INLINE TYPE 1
RETURN 
  ((yr-1900)*10000 + 0301 (DATE)) +
  ((((170-((YR MOD 19*3510+((((YR/100)*733+363)/25) + (YR/400))*319)/330) MOD 29+(YR+(YR/4)-(YR/100)+(YR/400))*57)/7)*7 MOD 57)-1)
;

REPLACE FUNCTION easter_sunday(cdate DATE)
RETURNS DATE
SPECIFIC easter_sunday_DT
RETURNS NULL ON NULL INPUT
CONTAINS SQL
DETERMINISTIC
COLLATION INVOKER
INLINE TYPE 1
RETURN easter_sunday(EXTRACT(YEAR FROM cdate))
;

REPLACE FUNCTION easter_sunday(cdate TIMESTAMP)
RETURNS DATE
SPECIFIC easter_sunday_TS
RETURNS NULL ON NULL INPUT
CONTAINS SQL
DETERMINISTIC
COLLATION INVOKER
INLINE TYPE 1
RETURN easter_sunday(EXTRACT(YEAR FROM cdate))
;

and Advent Sunday:

— advent sunday = the fourth Sunday before December 25

REPLACE FUNCTION advent_sunday(yr INT)
RETURNS DATE
SPECIFIC advent_sunday_I
RETURNS NULL ON NULL INPUT
CONTAINS SQL
DETERMINISTIC
COLLATION INVOKER
INLINE TYPE 1
RETURN 
   ((yr - 1900) * 10000 + 1224 (DATE)) - ((((yr - 1900) * 10000 + 1224 (DATE)) - DATE '0001-01-07') MOD 7) - 3*7
;
REPLACE FUNCTION advent_sunday(cdate DATE)
RETURNS DATE
SPECIFIC advent_sunday_DT
RETURNS NULL ON NULL INPUT
CONTAINS SQL
DETERMINISTIC
COLLATION INVOKER
INLINE TYPE 1
RETURN
   advent_sunday(EXTRACT(YEAR FROM cdate))
;
REPLACE FUNCTION advent_sunday(cdate TIMESTAMP)
RETURNS DATE
SPECIFIC advent_sunday_TS
RETURNS NULL ON NULL INPUT
CONTAINS SQL
DETERMINISTIC
COLLATION INVOKER
INLINE TYPE 1
RETURN
   advent_sunday(EXTRACT(YEAR FROM cdate))
;

Btw, you can also use Excel to create all those columns :-)