linq compare column data between two dates

Question

I need to compare the scores of a user between last week and two weeks ago.

i have a table like so Scores

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

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

user   score    subject     date         increase/decrease
 2       10      math     21/10/2012       +5
 2       10      science  23/10/2012       -4

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.

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);

from s in Scores where s.scoredate >= startOneWeekAgo && s.scoredate < endOneWeekAgo

this results in. This is what I have so far. Help would be appreciated.

user score subject
2     10    math
2     5     science

Answer 1

DateTime beginningOfWeek = DateTime.Now.BeginningOfWeek();
DateTime twoWeeksAgo = beginningOfWeek.AddDays(-14);
DateTime endOfLastWeek = beginningOfWeek.AddMilliseconds(-1);

var query = from s in scores
            where s.Date >= twoWeeksAgo && s.Date <= endOfLastWeek
            orderby s.Date
            group s by new { s.User, s.Subject } into g
            select new
                {
                    User = g.Key.User,
                    Subject = g.Key.Subject,
                    Date = g.Last().Date,
                    Diff = g.Last().Score - g.First().Score
                };

Thus you always selecting only two last scores (two weeks ago and one week ago), you will have only two records in each group.

If you will have only one week's results in database, then difference will be zero, because last and first entry in group will be the same.

Also you can use some DateTime extension for obtaining beginning of week:

public static class DateExtensions
{
    public static DateTime BeginningOfWeek(this DateTime date, 
                           DayOfWeek firstDayOfWeek = DayOfWeek.Monday)
    {
        if (date.DayOfWeek == firstDayOfWeek)
            return date.Date;

        DateTime result = date.AddDays(-1);

        while (result.DayOfWeek != firstDayOfWeek)
            result = result.AddDays(-1);

        return result.Date;
    }
}

And one more thing - try to avoid declaring several variables at once.