Computing week in Teradata

Database
Enthusiast

Computing week in Teradata

Hi,

I want to create a scheduled process that can select data for the prior week.Week for the purpose of this process is defined as Last Saturday to last to last Sunday.

Example if i am running the extract on any weekday 3/17/2009(Tuesday)..i need to select any records created between last to last Sunday(3/8/2009) and last saturday(3/14/2009) .

Week is always defined as Last to last Sunday to last Saturday, no matter which day of the current week the process runs.

Please advise.

Sam
6 REPLIES
Enthusiast

Re: Computing week in Teradata

I did it the following way.Is there anything wrong with what i am doing below?

Is there a better way guys?

select *
from Table1
inner join
(
select max(dt1) as dt1,max(dt2) as dt2 from
(
select calendar_date as dt1,cast(null as date) as dt2 from sys_calendar.calendar where day_of_week=1
--order by calendar_date desc
and calendar_date between date-14 and date-7
union
select cast(null as date) dt1,calendar_date as dt2 from sys_calendar.calendar where day_of_week=7
--order by calendar_date desc
and calendar_date between date-7 and date
) tbl
) tbl2
table1.create_dt between tbl2.dt1 and tbl2.dt2
Enthusiast

Re: Computing week in Teradata


Hi

The following query gives the desired result

You can replace '2009-03-17' with the other dates and check

SEL CAST('2009-03-17'-(DAY_OF_WEEK) AS DATE),CAST ('2009-03-17'-(DAY_OF_WEEK+6) AS DATE)FROM sys_calendar.calendar
WHERE calendar_date='2009-03-17'

cheers:-)
Enthusiast

Re: Computing week in Teradata

If i use the above query, the year part is not computed properly.

Here are the results

StartDt EndDt

03/08/3909 03/14/3909
Enthusiast

Re: Computing week in Teradata

I have changed the query as follows and it does work now

SEL
CAST ('2009-03-18' as date) - (DAY_OF_WEEK+6)
,cast('2009-03-18' as date) - (DAY_OF_WEEK)
FROM sys_calendar.calendar
WHERE calendar_date='2009-03-18'
Enthusiast

Re: Computing week in Teradata

Samit,

Use casting as follows:

SEL
cast (CAST ('2009-03-18' as date) - (DAY_OF_WEEK+6) as date)
,cast ( cast('2009-03-18' as date) - (DAY_OF_WEEK) as date )
FROM sys_calendar.calendar
WHERE calendar_date='2009-03-18'

cheers:-)
Senior Apprentice

Re: Computing week in Teradata

Hi Sam,
the best way to write a date literal is DATE '2009-03-18', this will always work.

So there's no need for
CAST ('2009-03-18' as date)

And of course in your query you might simply use calendar_date.

Dieter