title: Find last year fiscal year data with calendar table SQL
body: <p>My database has a calendar table that provides the fiscal year, period, etc. based on date (using Teradata with SQLAssistant). I'm trying to get last year's data in a SQL query which I thought would be as simple as adding a -1 in the where clause but it isn't working. I don't want to hard code a date in as I don't want to remember to update the query when the fiscal year changes.</p>
  
  <p>Simplified query that works and brings back sales data for all fiscal years:</p>
  
  <pre><code>Select s.sales, c.fiscalPeriod, c.fiscalYear
  from sales s
  join calendar c
  on s.salesDate = c.calDate
  </code></pre>
  
  <p>What I've tried, but it's bringing back no data:</p>
  
  <pre><code>Select s.sales, c.fiscalPeriod, c.fiscalYear
  from sales s
  join calendar c
  on s.salesDate = c.calDate
  where c.fiscalYear=c.fiscalYear-1
  </code></pre>
  
  <p>And this:</p>
  
  <pre><code>Select s.sales, c.fiscalPeriod, c.fiscalYear, c.fiscalYear-1 AS lastYear
  from sales s
  join calendar c
  on s.salesDate = c.calDate
  where c.fiscalYear=lastYear
  </code></pre>
  
  <p>As is probably obvious, I'm fairly new to SQL. Any help would be greatly appreciated.</p>