Group by date range , count and sort within each group LINQ

Question

I have a collection of dates stored in my object. This is sample data. In real time, the dates will come from a service call and I will have no idea what dates and how many will be returned:

var ListHeader = new List<ListHeaderData>
{
    new ListHeaderData
    {
        EntryDate = new DateTime(2013, 8, 26)
    },
    new ListHeaderData
    {
        EntryDate = new DateTime(2013, 9, 11)
    },
    new ListHeaderData
    {
        EntryDate = new DateTime(2013, 1, 1)
    },
    new ListHeaderData
    {
        EntryDate = new DateTime(2013, 9, 15)
    },
    new ListHeaderData
    {
        EntryDate = new DateTime(2013, 9, 17)
    },
    new ListHeaderData
    {
        EntryDate = new DateTime(2013, 9, 5)
    },
};

I now need to group by date range like so:

Today (1) <- contains the date 9/17/2013 and count of 1
within 2 weeks (3) <- contains dates 9/15,9/11,9/5 and count of 3
More than 2 weeks (2) <- contains dates 8/26, 1/1 and count of 2

this is my LINQ statement which doesn't achieve what I need but i think i'm in the ballpark (be kind if I'm not):

var defaultGroups = from l in ListHeader
                group l by l.EntryDate into g
                orderby g.Min(x => x.EntryDate)
                select new { GroupBy = g };

This groups by individual dates, so I have 6 groups with 1 date in each. How do I group by date range , count and sort within each group?

Answer 1

I suppose this depends on how heavily you plan on using this. I had/have a lot of reports to generate so I created a model IncrementDateRange with StartTime, EndTime and TimeIncrement as an enum.

The time increment handler has a lot of switch based functions spits out a list of times between the Start and End range based on hour/day/week/month/quarter/year etc.

Then you get your list of IncrementDateRange and in linq something like either:

TotalsList = times.Select(t => new RetailSalesTotalsListItem()
{
    IncrementDateRange = t,
    Total = storeSales.Where(s => s.DatePlaced >= t.StartTime && s.DatePlaced <= t.EndTime).Sum(s => s.Subtotal),
})

or

TotalsList = storeSales.GroupBy(g => g.IncrementDateRange.StartTime).Select(gg => new RetailSalesTotalsListItem()
{
    IncrementDateRange = times.First(t => t.StartTime == gg.Key),
    Total = gg.Sum(rs => rs.Subtotal),
}).ToList(),

Answer 2

Do you specifically want to achieve the solution in this way? Also do you really want to introduce spurious properties into your class to meet these requirements?

These three lines would achieve your requirements and for large collections willbe more performant.

var todays = listHeader.Where(item => item.EntryDate == DateTime.Today);

var twoWeeks = listHeader.Where(item => item.EntryDate < DateTime.Today.AddDays(-1) 
                                && item.EntryDate >= DateTime.Today.AddDays(-14));

var later = listHeader.Where(item => item.EntryDate < DateTime.Today.AddDays(-14));

also you then get the flexibility of different groupings without impacting your class.

[Edit: in response to ordering query]

Making use of the Enum supplied above you can apply the Union clause and OrderBy clause Linq extension methods as follows:

var ord = todays.Select(item => new {Group = DateRange.Today, item.EntryDate})
          .Union(
          twoWeeks.Select(item => new {Group = DateRange.LessThanTwoWeeks, item.EntryDate}))
          .Union(
          later.Select(item => new {Group = DateRange.MoreThanTwoWeeks, item.EntryDate}))
          .OrderBy(item => item.Group);

Note that I'm adding the Grouping via a Linq Select and anonymous class to dynamically push a Group property again not effecting the original class. This produces the following output based on the original post:

Group            EntryDate
Today            17/09/2013 00:00:00
LessThanTwoWeeks 11/09/2013 00:00:00
LessThanTwoWeeks 15/09/2013 00:00:00 
LessThanTwoWeeks 05/09/2013 00:00:00 
MoreThanTwoWeeks 26/08/2013 00:00:00 
MoreThanTwoWeeks 01/01/2013 00:00:00 

and to get grouped date ranges with count:

var ord = todays.Select(item => new {Group = DateRange.Today, Count=todays.Count()})
          .Union(
          twoWeeks.Select(item => new {Group = DateRange.LessThanTwoWeeks, Count=twoWeeks.Count()}))
          .Union(
          later.Select(item => new {Group = DateRange.MoreThanTwoWeeks, Count=later.Count()}))
          .OrderBy(item => item.Group);

Output is:

Group            Count
Today            1 
LessThanTwoWeeks 3 
MoreThanTwoWeeks 2 

Answer 3

Introduce array, which contains ranges you want to group by. Here is two ranges - today (zero days) and 14 days (two weeks):

var today = DateTime.Today;
var ranges = new List<int?> { 0, 14 };

Now group your items by range it falls into. If there is no appropriate range (all dates more than two weeks) then default null range value will be used:

var defaultGroups = 
      from h in ListHeader
      let daysFromToday = (int)(today - h.EntryDate).TotalDays
      group h by ranges.FirstOrDefault(range => daysFromToday <= range) into g
      orderby g.Min(x => x.EntryDate)
      select g;

UPDATE: Adding custom ranges for grouping:

var ranges = new List<int?>();
ranges.Add(0); // today
ranges.Add(7*2); // two weeks
ranges.Add(DateTime.Today.Day); // within current month
ranges.Add(DateTime.Today.DayOfYear); // within current year
ranges.Sort();

Answer 4

How about doing this?

Introduce a new property for grouping and group by that.

class ListHeaderData
{
    public DateTime EntryDate;
    public int DateDifferenceFromToday
    {
        get
        {
            TimeSpan difference = DateTime.Today - EntryDate.Date;
            if (difference.TotalDays == 0)//today
            {
                return 1;
            }
            else if (difference.TotalDays <= 14)//less than 2 weeks
            {
                return 2;
            }
            else
            {
                return 3;//something else
            }
        }
    }
}

Edit: as @servy pointed in comments other developers may confuse of int using a enum will be more readable.

So, modified version of your class would look something like this

class ListHeaderData
{
    public DateTime EntryDate;
    public DateRange DateDifferenceFromToday
    {
        get
        {
            //I think for this version no comments needed names are self explanatory
            TimeSpan difference = DateTime.Today - EntryDate.Date;
            if (difference.TotalDays == 0)
            {
                return DateRange.Today;
            }
            else if (difference.TotalDays <= 14)
            {
                return DateRange.LessThanTwoWeeks;
            }
            else
            {
                return DateRange.MoreThanTwoWeeks;
            }
        }
    }
}

enum DateRange
{ 
    None = 0,
    Today = 1,
    LessThanTwoWeeks = 2,
    MoreThanTwoWeeks = 3
}

and use it like this

 var defaultGroups = from l in ListHeader
     group l by l.DateDifferenceFromToday into g // <--Note group by DateDifferenceFromToday
     orderby g.Min(x => x.EntryDate)
     select new { GroupBy = g };