title: Can I create a SQL view to show "fake" data
body: <p>My boss wants me to create a view in SQL to show data that isn't there. Is that possible? Here's what I need:</p>
  
  <p>I need to show our internal currency conversion rate for any sourceCur that = targetCur. We only have this data for the year 2011, period 1. I need it to show all 12 periods for every year from 2011 to 2017. The value will always be the same (1). Here are the columns yearNum, preiodNum, sourceCur, targetCur, convRate.</p>
  
  <p>A simple SELECT statement:</p>
  
  <pre><code>SELECT * 
  FROM dbo.CurrencyConversionRates
  WHERE (sourceCur = targetCur)
  </code></pre>
  
  <p>The results are:</p>
  
  <p><img src="https://i.stack.imgur.com/4fK7P.png" alt="Results"></p>
  
  <p>The value of convRate will always be 1 in this case because the currency is the same. I just need to project the data we have (2011 period 1) to all 12 periods for each year from 2011 to 2017. My boss does not want to add the data into the table, he wants a view. </p>
  
  <p>Is this possible?</p>
  
  <p><strong>EDIT FROM COMMENTS</strong></p>
  
  <pre><code>WITH future( yearNum , periodNum) AS (
      SELECT DISTINCT 
          CalendarYear , 
          fiscalPeriodNum 
      FROM 
          CALENDAR 
      WHERE CalendarYear BETWEEN 2011 AND YEAR(GETDATE())), 
  
  currencies( sourceCur , targetCur) AS ( 
      SELECT DISTINCT 
          sourceCur , 
          targetCur 
      FROM 
          CurrencyConversionRates 
      WHERE targetCur = sourceCur)
  
  SELECT 
      f.yearNum , 
      f.periodNum , 
      c.sourceCur , 
      c.targetCur , 
      '1' AS conversionRate 
  FROM 
      future AS f 
  CROSS JOIN currencies AS c 
  ORDER BY 
      f.yearNum , 
      f.periodNum;
  </code></pre>