title: linq compare column data between two dates
body: <p>I need to compare the scores of a user between last week and two weeks ago. </p>
  
  <p>i have a table like so
  Scores</p>
  
  <pre><code>user   score  subject       date        
   2      10     math       21/10/2012
   2       5     science    23/10/2012
   2       5     math       16/10/2012
   2       9     science    15/12/2012
  </code></pre>
  
  <p>I need to produce a query that shows last week's score and whether it is an increase or decrease from 2 weeks ago's score</p>
  
  <pre><code>user   score    subject     date         increase/decrease
   2       10      math     21/10/2012       +5
   2       10      science  23/10/2012       -4
  </code></pre>
  
  <p>The date column doesn't need to be included in the query
  I already have the code to get week range from last week to two weeks ago. I'm having trouble comparing the two dates however.</p>
  
  <pre><code>DateTime date = DateTime.Now;
  DateTime startOneWeekAgo = date.AddDays(-7).Date.AddDays(-(int)date.DayOfWeek),
           endOneWeekAgo = startOneWeekAgo.AddDays(7);
  
  DateTime startTwoWeeksAgo = startOneWeekAgo.AddDays(-7),
           endTwoWeeksAgo = endOneWeekAgo.AddDays(-7);
  </code></pre>
  
  <blockquote>
    <p>from s in Scores where s.scoredate >= startOneWeekAgo &amp;&amp; s.scoredate &lt;
    endOneWeekAgo</p>
  </blockquote>
  
  <p>this results in. This is what I have so far. Help would be appreciated.</p>
  
  <pre><code>user score subject
  2     10    math
  2     5     science
  </code></pre>