week from date

Database
Enthusiast

week from date

I have a table with event date and cost (integer). I want to find the total cost per year, per week.

i.e 

2018 Week1 200000

2018 Week2 100000

2018 Week3 500000

2018 Week4 700000

2018 Week5 800000

2018 Week6 990000

2018 Week7 720000

2018 Week8 2100000

 

One easiest way is to join with sys.sys_Calendar and get the week from the calendar table. 

Is it possible to do this with out joining to sy_calendar?


Accepted Solutions
Teradata Employee

Re: week from date

It is easy if you know what day you want to consider the first week of the year.  For 2018, the first week would start on Jan. 1; other years it might not start until Jan. 2 or 3.  There is an ISO standard for determining this, but not everyone follows it, so you need to know what your company decides is the first week.  Then you also need to know if the first day of the week is Sunday or Monday.

If we let d be the date of the first day of the first week of the year, then:

   Select  (Event_Date - d)/7 as WeekNbr, sum(Cost) as TotalCost from ...

   group by WeekNbr order by WeekNbr;

 

For 2018 d could be DATE'2017-12-31' if the week starts on Sunday or DATE'2018-01-01' if it starts on Mondays.  For 2019 d could be DATE'2018-12-30' or DATE'2018-12-31'.

There is also some intricate arithmetic you could use to calculate what d should be.  For starters, this will give you the Sunday before or at the beginning of the current year:
Select trunc(DATE,'YYYY') - dow from
(select extract(year from DATE) - 1 as y, ( 36+y+y/4-y/100+y/400 ) mod 7 as dow) X;

So if you are running for the current year - whatever year it may be - you might say:

Select  (Event_Date - (Trunc(DATE,'YYYY')-dow))/7 as WeekNbr, sum(Cost) as TotalCost
From ...
Cross Join
  (select extract(year from DATE) - 1 as y, ( 36+y+y/4-y/100+y/400 ) mod 7 as dow) X
group by WeekNbr order by WeekNbr;

1 ACCEPTED SOLUTION
6 REPLIES
Teradata Employee

Re: week from date

It is easy if you know what day you want to consider the first week of the year.  For 2018, the first week would start on Jan. 1; other years it might not start until Jan. 2 or 3.  There is an ISO standard for determining this, but not everyone follows it, so you need to know what your company decides is the first week.  Then you also need to know if the first day of the week is Sunday or Monday.

If we let d be the date of the first day of the first week of the year, then:

   Select  (Event_Date - d)/7 as WeekNbr, sum(Cost) as TotalCost from ...

   group by WeekNbr order by WeekNbr;

 

For 2018 d could be DATE'2017-12-31' if the week starts on Sunday or DATE'2018-01-01' if it starts on Mondays.  For 2019 d could be DATE'2018-12-30' or DATE'2018-12-31'.

There is also some intricate arithmetic you could use to calculate what d should be.  For starters, this will give you the Sunday before or at the beginning of the current year:
Select trunc(DATE,'YYYY') - dow from
(select extract(year from DATE) - 1 as y, ( 36+y+y/4-y/100+y/400 ) mod 7 as dow) X;

So if you are running for the current year - whatever year it may be - you might say:

Select  (Event_Date - (Trunc(DATE,'YYYY')-dow))/7 as WeekNbr, sum(Cost) as TotalCost
From ...
Cross Join
  (select extract(year from DATE) - 1 as y, ( 36+y+y/4-y/100+y/400 ) mod 7 as dow) X
group by WeekNbr order by WeekNbr;

Enthusiast

Re: week from date

Thank you  Coleman, 

 

i couldnt understand this logic 36+y+y/4-y/100+y/400, is it possible to elaborate further on this logic?

Teradata Employee

Re: week from date

Yes, that is very strange logic, based on an algorithm I learned long ago from a manual for a calculator.  I do not know who came up with it, nor do I have any idea how to prove it, but it works for every date since October, 1582.  The general formula for day-of-week is, in awk language:

function dow(century,year,month,day) {
	if ((month -= 2) <= 0) {
		month += 12
		if (--year < 0) {
			year = 99
			--century
		}
	}
	DayOfWk = ( day + int((13*month-1)/5) + int(year/4) + year + int(century/4) - 2*century ) % 7
	if (DayOfWk < 0) DayOfWk += 7;
	return DayOfWk
}

In this form, Sunday is 0 and Saturday is 7.

Highlighted
Teradata Employee

Re: week from date

One more point on the subject of week days: the above algorithm works everywhere, for every date in the last 500+ years, but there is an even easier one that works well in Teradata because it is so easy to do date arithmetic in Teradata.  That is, subtract Jan. 7 of the year 1 from the current date, divide by 7 and take the remainder (modulo 7) to get a number from 0 (Sunday) to 6 (Saturday).  So this query could also be expressed as:

 

Select  (Event_Date - (Trunc(DATE,'YYYY') - ((Trunc(DATE,'YYYY')-Date'0001-01-07') mod 7)))/7 as WeekNbr, sum(Cost) as TotalCost
From ...
group by WeekNbr order by WeekNbr;

 

I don't know if the optimizer would recognize (Trunc(DATE,'YYYY') - ((Trunc(DATE,'YYYY')-Date'0001-01-07') mod 7)) as a constant and thus avoid computing it for each row, but you could also put this expression in a cross-joined derived table as above to make that clear.  In any case, doing it this way avoids the mysterious arithmetic formula.

Enthusiast

Re: week from date

Coleman,

 

Thank you so much for the great information. I will implement your logic and see how it works in my code. 

Thanks Again

Junior Contributor

Re: week from date

Each column in sys_calendar.calendar has a matching UDF:

week_of_year -> td_week_of_year(event_date)

To get an ISO week calculation you can apply the Business Calendar UDFs:

 

YearNumber_Of_Calendar(event_date, 'ISO')
WeekNumber_Of_Year(event_date, 'ISO')

Caution: you must use the function to get the year because a date around new year might belong to the previous or following ISO year, e.g. '2017-01-01' is part of ISO week 201652.

 

 

Or use TO_CHAR:

 

To_Char(event_date, 'IYYYIW')

Or return the monday of a week:

Trunc(event_date, 'iw')