3

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

user3438498
  • 211
  • 3
  • 11
  • 21
  • Please, can you improve the question, that data exactly do you need? Data (tables and databases) which changed last week or what? – Rocketq Jun 01 '15 at 17:46

2 Answers2

7

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
dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • Very cool, didn't know about that function. BTW, I think you mean -7 for your previous saturday, not 8. – Andrew Jun 01 '15 at 18:06
  • Or, it could just be that I'm incapable of reading sometimes, and didn't catch that you were passing in Sunday for the parameter. – Andrew Jun 01 '15 at 18:32
  • @user3438498: TD13 is an ancient version :-) I added a solution for 13, too. – dnoeth Jun 01 '15 at 20:01
0

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
Andrew
  • 8,445
  • 3
  • 28
  • 46