title: Creating a trend line from data set SQL
body: <p>The code below returns the number of resolved tickets and the number of opened tickets for a period (period is YYYY,WW) going back a certain number of days. For example if @NoOfDays is 7:</p>
  
  <blockquote>
    <p>resolved | opened | week | year | period</p>
    
    <p>56 | 30 | 13 | 2012 | 2012, 13</p>
    
    <p>237 | 222 | 14 | 2012 | 2012, 14</p>
  </blockquote>
  
  <p>'resolved' and 'opened' are graphed on lines (y) over period (x). I would like to add another column 'trend' that would return a number that when graphed over period, will be a trend line (simple linear regression). <em>I <strong>do</strong> want to use both sets of values as one data source for the trend.</em> </p>
  
  <p>This is the code I have:</p>
  
  <pre><code>SELECT a.resolved, b.opened, a.weekClosed AS week, a.yearClosed AS year,
      CAST(a.yearClosed as varchar(5)) + ', ' + CAST(a.weekClosed as varchar(5)) AS period
  FROM 
      (SELECT TOP (100) PERCENT COUNT(DISTINCT TicketNbr) AS resolved, { fn WEEK(date_closed) } AS weekClosed, { fn YEAR(date_closed) } AS yearClosed
      FROM v_rpt_Service
      WHERE (date_closed &gt;= DateAdd(Day, DateDiff(Day, 0, GetDate()) - @NoOfDays, 0))
      GROUP BY { fn WEEK(date_closed) }, { fn YEAR(date_closed) }) AS a 
  LEFT OUTER JOIN
      (SELECT TOP (100) PERCENT COUNT(DISTINCT TicketNbr) AS opened, { fn WEEK(date_entered) } AS weekEntered, { fn YEAR(date_entered) 
      } AS yearEntered
      FROM v_rpt_Service AS v_rpt_Service_1
      WHERE        (date_entered &gt; = DateAdd(Day, DateDiff(Day, 0, GetDate()) - @NoOfDays, 0))
      GROUP BY { fn WEEK(date_entered) }, { fn YEAR(date_entered) }) AS b ON a.weekClosed = b.weekEntered AND a.yearClosed = b.yearEntered
  ORDER BY year, week
  </code></pre>
  
  <p><strong>Edit:</strong></p>
  
  <p>According to serc.carleton.edu/files/mathyouneed/best_fit_line_dividing.pdf, it seems that I want to break the data in half, then calculate the average. Then I need to find the best fit line, and use the slope and the y-intercept to calculate the values needed to return in 'trend' using <code>y = mx + b</code>?</p>
  
  <p>I know this is very possible in SQL, however, the program I am inserting the SQL into has limitations on what I can do. </p>
  
  <p><strong>The red and blue dots are the numbers I am returning now(opened and resolved). I need to return a value for every period in 'trend' in order to create the purple line. (this image is hypothetical)</strong></p>
  
  <p><img src="https://i.stack.imgur.com/4YwtN.png" alt="Hypothetical Chart"></p>