All Forums Database
Nexsn 3 posts Joined 06/13
04 Jun 2013
Get last full 20 weeks with function ADD_MONTHS(date - EXTRACT(day FROM date) +0, 0)

Hi community,
first of all, sorry for my bad english, I'm doing by best!
I've a question. I need to get database-entries for the last full 20 weeks, but the query should be dynamic.

Example for data-entries for the last full 12 months:
CAST(i.Timestamp_TS AS DATE) > ADD_MONTHS(date - EXTRACT(day FROM date) +0, -13) +0
I need a funktion like this but like I said, for the last full 20 weeks. No Matter when I start the query.
But I guess i cannot do this with that function!?
Is it possible to make this without using table "Sys_Calendar.Calendar"?
Thanks for reading and answering!

dnoeth 4628 posts Joined 11/04
04 Jun 2013

This will calculate the previous monday (when your week starts on sunday change to DATE '0001-01-07'):

datecol - ((datecol - DATE '0001-01-01') MOD 7)

And now it's easy to substract 20*7 days :-)


Nexsn 3 posts Joined 06/13
04 Jun 2013

First of all, thanks for your help!
I've to say that I'm new the teradata and the whole database thing!
I do not completly understand this function.
Sure I know what mod is. But why subtract 0001-01-07 from my column and then mod 7?
For example today. That makes no sense to me :(
2013-06-05 - Date '0001-01-07' --> !?
and shame to me, how can i substract 20*7 days in this case?
my query:
tabelle i
CAST(i.Abschluss_Erledigt_TS AS DATE) <= current_date
CAST(i.Abschluss_Erledigt_TS AS DATE) >= (CAST(i.Abschluss_Erledigt_TS AS DATE) - ((CAST(i.Abschluss_Erledigt_TS AS DATE) - DATE '0001-01-07') MOD 7)-20*7)
order by 1 desc
Thanks again for your help!

dnoeth 4628 posts Joined 11/04
05 Jun 2013

Jeder fängt mal neu an, kein Problem :-)
Add/substract a number of days from a DATE is just DATE +/- x.
(datecol - DATE '0001-01-01') returns the number of days between those dates.
DATE '0001-01-01' is a known monday (you could use any other monday instead), thus the difference modulo 7 returns 0 = monday to 7 = sunday. Substract that from your current date and you'll get the previous monday. Substract 20*7 and you'll get the monday 20 weeks before.
Your query is almost correct, you just have to use CURRENT_DATE instead of i.Abschluss_Erledigt_TS. And i'd suggest removing the typecast from i.Abschluss_Erledigt_TS (which might help the optimizer to get better estmates):

   Abschluss_Erledigt_TS < CAST(CURRENT_DATE + 1 AS TIMESTAMP) -- next day midnight
   Abschluss_Erledigt_TS >= CAST(CURRENT_DATE - ((CURRENT_DATE - DATE '0001-01-07') MOD 7) -20*7 AS TIMESTAMP) -- midnight monday 20 weeks before




Nexsn 3 posts Joined 06/13
05 Jun 2013

Hab mich nicht getraut auf deutsch zu schreiben weil ja englisches Forum :) - deshalb auch Englisch weiter.
thanks again for your help! at this time, I'm not into this database-thinking, I hope it will become better ;)
query works! for testing I replaced test-dates instead of current_date.
there is only 1 little problem i can fix on my own: i get the sunday before the first monday, i guess because the english time-calulation.
But it should be fixed easy with +1 .
danke nochmal und schönen tag :)

You must sign in to leave a comment.