Calculate previous week data in Teradata

teradata weekday
user3438498 picture user3438498 · Jun 1, 2015 · Viewed 7.5k times · Source

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

Answer

dnoeth picture dnoeth · Jun 1, 2015

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

Related questions

SQL SELECT multi-columns INTO multi-variable

I'm converting SQL from Teradata to SQL Server in Teradata, they have the format SELECT col1, col2 FROM table1 INTO @variable1, @variable2 In SQL Server, I found SET @variable1 = ( SELECT col1 FROM table1 ); That only allows a single column/variable …

Read More
What is ROWS UNBOUNDED PRECEDING used for in Teradata?

I am just starting on Teradata and I have come across an Ordered Analytical Function called "Rows unbounded preceding" in Teradata. I tried several sites to learn about the function but all of them uses a complicated example explaining the …

Read More
Find which rows have different values for a given column in Teradata SQL

I am trying to compare two addresses from the same ID to see whether they match. For example: Id Adress Code Address 1 1 123 Main 1 2 123 Main 2 1 456 Wall 2 2 456 Wall 3 1 789 Right 3 2 100 Left I'm just trying to figure out whether the address for each ID …

Read More