title: TERADATA : Joining Tables
body: <p>I have a table which I have generated where the information is rolled up to aggregates per day
  I have a second table which can be considered a calendar table which allows me when joining the tables to get the week number of the aggregates</p>
  
  <p>The date in the tables are all integers e.g. 20130501 (YYYYMMDD)</p>
  
  <p>When i create the SQL below (generalized) it works fine but now i want to schedule this script to run once a week to get the previous week and am getting an error</p>
  
  <p><strong>Script works fine</strong></p>
  
  <pre><code>    SELECT *
      FROM TABLE_1 as sample
      INNER JOIN TABLE_2 as calendar
      ON sample.day = calendar.day
      WHERE sample.day &gt;= 20130501
      AND sample.day &lt;= 20130507
  </code></pre>
  
  <p><strong>Script Fails</strong></p>
  
  <pre><code>    SELECT *
      FROM TABLE_1 as sample
      INNER JOIN TABLE_2 as calendar
      ON sample.day = calendar.day
      -- &gt;= Previous Monday Last week
      WHERE sample.day &gt;= CAST(CAST(((DATE-DAYOFWEEK(DATE)-5) (FORMAT 'YYYYMMDD')) AS CHAR(8)) AS INT)
     --  &lt;= Previous Sunday Last week
      AND   sample.day &lt;= CAST(CAST(((DATE-DAYOFWEEK(DATE)+ 1) (FORMAT 'YYYYMMDD')) AS CHAR(8)) AS INT) 
  </code></pre>
  
  <p>The Error message I'm getting on the second script is</p>
  
  <p><em>Teradata ODBC Teradata Driver Syntax Error: expected something between ')' and '-'</em></p>
  
  <p>Thank you all for your time
  It has been troubling me for a few days now</p>