title: Using previous year's data within the same query
body: <p>Below is my current query, I have a CTE that calculates the revenue for each of my fiscal periods grouped by the clinic and their respective transactions. Followed by a query that looks at the individual clinic's worked days and finding the daily average (calculating the current days) and projected revenue based on the expected # of days. </p>
  
  <p>This query works fine as is; however I need to add a few more columns. I would like to also show the Revenue of the previous year, so in this case I have limited the query to only showing 2016 and period 9 and the previous year's revenue should show for 2015's period 9 and also will need to see the previous year's days as well (which I imagine will be a similar process).</p>
  
  <pre><code>WITH CTE_Revenue AS(
  SELECT  c.Clinic,
          c.ClinicID,
          SUM(td.Amount)*-1 AS Revenue,
          p.PeriodID,
          p.FiscalYear
  FROM Trans.TransactionHeader th
  JOIN Clinic.[Master] c
      ON (th.ClinicID = c.ClinicID)
  JOIN Trans.TransactionDetail td
      ON (th.ClinicID = td.ClinicID AND th.TranNum = td.TranNum)
  JOIN Clinic.EOD e
      ON (th.ClinicID = e.ClinicID AND th.TranNum BETWEEN e.StartTran AND e.EndTran)
  JOIN Clinic.Period p
      ON (CAST(e.TimeRan AS date) BETWEEN p.PeriodStart AND p.PeriodEnd)
  AND th.Impacts='C'
  GROUP BY c.Clinic, c.ClinicID, p.PeriodID, p.FiscalYear)
  
  SELECT  w.Clinic,
          w.Revenue,
          (w.Revenue / days.CurrentDays) AS DailyAverage,
          (w.Revenue / days.CurrentDays)*d.PeriodDays AS ProjectedRevenue
  FROM CTE_Revenue w
  JOIN Clinic.Dates d
      ON (w.ClinicID = d.ClinicID AND w.PeriodID = d.PeriodID AND w.FiscalYear = d.FiscalYear)
  JOIN (  SELECT DISTINCT
          td.ClinicID,
          COUNT(DISTINCT td.DateEntered) AS CurrentDays,
          p.PeriodID,
          p.FiscalYear
      FROM Trans.TransactionDetail td
      JOIN Clinic.Period p
          ON td.DateEntered BETWEEN p.PeriodStart AND p.PeriodEnd
      GROUP BY td.ClinicID, p.PeriodID, p.FiscalYear) AS days
      ON (w.ClinicID = days.ClinicID AND w.PeriodID=days.PeriodID AND w.FiscalYear = days.FiscalYear)
  WHERE w.FiscalYear = 2016
  AND w.PeriodID = 9
  </code></pre>
  
  <p>The end resulting SELECT statement, I imagine, will look something like this:</p>
  
  <pre><code>SELECT  w.Clinic,
          w.Revenue,
          (w.Revenue / days.CurrentDays) AS DailyAverage,
          (w.Revenue / days.CurrentDays)*d.PeriodDays AS ProjectedRevenue,
          PrevYear.Revenue,
          (PrevYear.Revenue / PrevYear.CurrentDays) AS PYDailyAverage,
          (PrevYear.Revenue / PrevYear.CurrentDays)*d.PeriodDays AS PYCalculated
  </code></pre>
  
  <p>This query might not be fully optimized, I'm still fairly new to SQL. Thanks for any advice and help ahead of time!</p>
  
  <p>EDIT: Here's an image of the table structures and relations using the SQL Server Diagrams:
  <a href="https://i.stack.imgur.com/2sSU3.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/2sSU3.jpg" alt="Table Diagram"></a></p>