Fiscal vs. Calendar Issue - Adjusting SYS_CALENDAR to Begin Monday

Database
N/A

Fiscal vs. Calendar Issue - Adjusting SYS_CALENDAR to Begin Monday

Good afternoon,

I'm currently trying to roll-up daily customer transaction data to the weekly level. I've joined my dataset to Teradata's SYS_CALENDAR.CALENDAR, and have used the 'WEEK_OF_YEAR' to bring daily data to the week level.

My issue is that I need to roll-up daily data beginnging on the first day of my company's fiscal year, which is a Monday. Is there any way to adjust the SYS_CALENDAR to consider the first day of a week as a Monday, not Sunday?

Does anyone have any alternative ideas as to how I might solve this issue?

I'm new to database management, so the more detail you are all able to provide, the better!

With thanks,

Arbie

6 REPLIES
Supporter

Re: Fiscal vs. Calendar Issue - Adjusting SYS_CALENDAR to Begin Monday

The question is if this change would be already sufficient for you?

How is the fiscal week definition in your company for the week which contains the 01.01. of a year?

Many customers have a separate calendar table where they are able to maintain specifics of theire country and company.

Enthusiast

Re: Fiscal vs. Calendar Issue - Adjusting SYS_CALENDAR to Begin Monday

I need to design a calendar view in which week should be Monday to Sunday whereas in Current Teradata calendar it's Sunday to Monday.

I am done with logic for DAY_OF_WEEK column but facing difficulty in WEEK_OF_YEAR column , here also week should be considered from Monday to Sunday and also boundry days are to be considered e.g. if first day of  sunday then it should come in last year's last week.

Please suggest.

Senior Apprentice

Re: Fiscal vs. Calendar Issue - Adjusting SYS_CALENDAR to Begin Monday

What definition of week are you trying to implement? ISO?

This is what i use:

REPLACE VIEW sys_calendar.calbasics_iso AS
SELECT
/*** Modify here for different start date ***/
DATE '1900-01-01' AS start_of_calendar,

cdate AS calendar_date,

((cdate - DATE '0001-01-01') MOD 7) + 1 (FORMAT '9') AS day_of_week,

EXTRACT(DAY FROM cdate) (FORMAT '99') AS day_of_month,

cdate - ((EXTRACT(YEAR FROM cdate) - 1900) * 10000 + 0101 (DATE)) + 1 (FORMAT '9999') AS day_of_year,

cdate - start_of_calendar + 1 AS day_of_calendar,

(day_of_month - 1) / 7 + 1 (FORMAT '9') AS WEEKDAY_OF_MONTH,

(day_of_month - day_of_week + 6) / 7 (FORMAT '9') AS week_of_month,

(day_of_calendar - day_of_week + 6) / 7 AS week_of_calendar,

(month_of_year - 1) MOD 3 + 1 AS month_of_quarter,

EXTRACT(MONTH FROM cdate) AS month_of_year,

month_of_year + 12 * (year_of_calendar - EXTRACT(YEAR FROM start_of_calendar)) AS month_of_calendar,

(month_of_year + 2) / 3 AS quarter_of_year,

(month_of_year + 2) / 3 + 4 * (year_of_calendar - EXTRACT(YEAR FROM start_of_calendar)) AS quarter_of_calendar,

EXTRACT(YEAR FROM cdate) AS year_of_calendar,

CASE day_of_week
WHEN 1 THEN 'Monday'
WHEN 2 THEN 'Tuesday'
WHEN 3 THEN 'Wednesday'
WHEN 4 THEN 'Thursday'
WHEN 5 THEN 'Friday'
WHEN 6 THEN 'Saturday'
WHEN 7 THEN 'Sunday'
ELSE ''
END AS weekday,

/**ISO_temp: this week's thursday**/
cDate - Day_Of_Week + 4 AS ISO_temp,

EXTRACT (YEAR FROM ISO_temp) (FORMAT '9999') AS ISO_year,

((ISO_temp - ((EXTRACT(YEAR FROM ISO_temp) - 1900) * 10000 + 0101 (DATE))) / 7) + 1 (FORMAT '99')
AS ISO_week,

ISO_year || 'W' || ISO_week AS Week_ISO

FROM sys_calendar.caldates
;

REPLACE VIEW sys_calendar.ISO_calendar AS
SELECT
calendar_date,
day_of_week,
day_of_month,
day_of_year,
day_of_calendar,
WEEKDAY_OF_MONTH,
week_of_month,
week_of_calendar,
ISO_Week,
month_of_quarter,
month_of_year,
month_of_calendar,
year_of_calendar,
quarter_of_year,
quarter_of_calendar
FROM sys_calendar.calbasics_iso
;

Btw, in TD14 there's a new ISO calendar built-in...

Dieter

Enthusiast

Re: Fiscal vs. Calendar Issue - Adjusting SYS_CALENDAR to Begin Monday

Thanks this is what was required.

Enthusiast

Re: Fiscal vs. Calendar Issue - Adjusting SYS_CALENDAR to Begin Monday

Dieter,

What is the table/view name in TD14 for the ISO Calendar?

Enthusiast

Re: Fiscal vs. Calendar Issue - Adjusting SYS_CALENDAR to Begin Monday

You can use

Sys_Calendar.CALENDAR_TD_ISO_COMPATIBLE

OR

for ISO Business Calender

Sys_Calendar.ISOBCal

Khurram