Get last full 20 weeks with function ADD_MONTHS(date - EXTRACT(day FROM date) +0, 0)

Database
Fan

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:

WHERE

CAST(i.Timestamp_TS AS DATE) > ADD_MONTHS(date - EXTRACT(day FROM date) +0, -13) +0

AND

CAST(i.Timestamp_TS AS DATE) < CAST(CURRENT_DATE as DATE)

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!

Greets!

4 REPLIES
Senior Apprentice

Re: Get last full 20 weeks with function ADD_MONTHS(date - EXTRACT(day FROM date) +0, 0)

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 :-)

Dieter

Fan

Re: Get last full 20 weeks with function ADD_MONTHS(date - EXTRACT(day FROM date) +0, 0)

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:

select

i.Abschluss_Erledigt_TS

from

tabelle i

where

CAST(i.Abschluss_Erledigt_TS AS DATE) <= current_date

and

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!

Senior Apprentice

Re: Get last full 20 weeks with function ADD_MONTHS(date - EXTRACT(day FROM date) +0, 0)

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):

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

Dieter

Fan

Re: Get last full 20 weeks with function ADD_MONTHS(date - EXTRACT(day FROM date) +0, 0)

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 :)

LG

Philip