CODEWITHSUNDEEP
teradataweekday
user3438498
user3438498

Reputation: 211

Calculate previous week data in Teradata

How to calculate prior 7 days data (Sunday-Saturday of the previous week). on whatever day of week it is run it should always give Sunday-Saturday of the previous week. thanks

Upvotes: 3

Views: 10291

Answers (2)

dnoeth
dnoeth

Reputation: 60472

What's your Teradata release?

TD14 supports NEXT_DAY, which returns the first "weekday" later than the date specified:

SELECT NEXT_DAY(CURRENT_DATE, 'sun'),    -- next sunday
       NEXT_DAY(CURRENT_DATE, 'sun')-14, -- previous week's sunday
       NEXT_DAY(CURRENT_DATE, 'sun')-8   -- previous week's saturday

Edit:

In TD13 you can subtract the day of week to get the previous week's end date, e.g. (CURRENT_DATE - DATE '0001-01-01') MOD 7 + 1 returns 1 to 7 for Monday to Sunday based on the known Monday '0001-01-01'.

Modified to your needs (week starts on Sunday) this results in:

SELECT
   CURRENT_DATE - ((CURRENT_DATE - DATE '0001-01-07') MOD 7 + 7),  -- previous week's Sunday 
   CURRENT_DATE - ((CURRENT_DATE - DATE '0001-01-07') MOD 7 + 1)   -- previous week's Saturday

Upvotes: 7

Andrew
Andrew

Reputation: 8713

Another fine use for the calendar table:

select calendar_date
from
sys_calendar.calendar
where
week_of_calendar =
(select week_of_calendar from sys_calendar.calendar where calendar_date = current_Date) -1

Upvotes: 0

Related Questions