multiple group by using linq

Question

I need return just 2 lines in my query. One line with a string Today and a number of cases closed today, on my second line I need a string Last Week and a number of cases closed on the last week.

How I group with a range date?

Sum         Name                                              
----------- ----------
12           Today                                
33           Last Weeb                                        

Answer 1

How about this:

var caseCounts = Cases
    .Where(c => c.Date == today || (c.Date >= startOfLastWeek && c.Date <= endOfLastWeek))
    .GroupBy(c => c.Date == today ? "Today" : "Last Week")
    .Select(g => new {
        Name = g.Key, Sum = g.Count() 
    });

You would need to define the 3 dates (today, startOfLastWeek, endOfLastWeek) before hand, but it gives you the results you are after.

Answer 2

GROUP BY YEARWEEK(date) should work. Depending on your dbms, you might be able to use another function, or program your own.

http://www.tutorialspoint.com/sql/sql-date-functions.htm#function_yearweek