Reputation: 713
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
Upvotes: 0
Views: 1790
Reputation: 236328
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.
Upvotes: 1