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!
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.
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.
What definition of week are you trying to implement? ISO?
This is what i use:
REPLACE VIEW sys_calendar.calbasics_iso AS
/*** 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,
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'
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')
ISO_year || 'W' || ISO_week AS Week_ISO
REPLACE VIEW sys_calendar.ISO_calendar AS
Btw, in TD14 there's a new ISO calendar built-in...
You can use
for ISO Business Calender