LINQ & Grouping data by a date range

Question

Say I have the below set of data where I wish to group the Description column based on whether the dates for matching Descriptions are within a few seconds of each other.

enter image description here

The output I'm looking for is the Description and the minimum Date for that group. It is possible that the Description could be the same but the dates might be days different, in this case I would want two outputted rows.

In the case above take a look at the Description "TEST s" where I would want two outputted grouped rows

TEST s 2014-12-04 16:27:44.903

TEST s 2014-12-04 17:21:21.233

Is this possible using Linq?

so you want multiple group bys? http://stackoverflow.com/questions/5231845/c-sharp-linq-group-by-on-multiple-columns — Ric, Dec 11 '14 at 13:06
@Ric More of a Group, Min in TSQL terms but with separate lines where the dates are out of a range. To be honest I'm not sure how to term this in TSQL. — Phil Murray, Dec 11 '14 at 13:08
Can you give an example of how to group rows that are "within a few seconds of each other". The example you have the dates are the same within each group. — juharr, Dec 11 '14 at 13:18

Giorgos Betsos · Accepted Answer · 2014-12-11T13:52:26.450

Try this:

var q = from item in lstMyTable
        group item by item.ItemDate.ToString("MM/dd/yyyy HH:mm") into ItemGroup
        select new
        {
            Description = ItemGroup.First().Description,
            Date = ItemGroup.OrderBy(x => x.ItemDate).First().ItemDate
        };

The above linq query groups by date + hour + minutes, ignoring seconds. The OrderBy applied to ItemGroup ensures that we get the mininum date, as described in the OP.

With this input:

List<MyTable> lstMyTable = new List<MyTable>()
{
  new MyTable() { Description = "TEST s", ItemDate = new DateTime(2014, 12, 4, 16, 27, 11) },
  new MyTable() { Description = "TEST s", ItemDate = new DateTime(2014, 12, 4, 16, 27, 12) },
  new MyTable() { Description = "TEST s", ItemDate = new DateTime(2014, 12, 4, 16, 27, 13) },

  new MyTable() { Description = "TEST s", ItemDate = new DateTime(2014, 12, 4, 17, 21, 11) },
  new MyTable() { Description = "TEST s", ItemDate = new DateTime(2014, 12, 4, 17, 21, 12) },
  new MyTable() { Description = "TEST s", ItemDate = new DateTime(2014, 12, 4, 17, 21, 13) }
};

you get this result:

[0] = { Description = "TEST s", Date = {4/12/2014 4:27:11 pm} }
[1] = { Description = "TEST s", Date = {4/12/2014 5:21:11 pm} }

If you want to also group by description then simply substitue the group by clause with sth like this:

group item by new 
{ 
   a = item.ItemDate.ToString("MM/dd/yyyy HH:mm") ,
   b = item.Description 
} into ItemGroup

Finally, by converting the Datetime field to Ticks, you can fiddle with the required time distance between separate records, e.g.

group item by new 
{ 
    a = item.ItemDate.Ticks.ToString().Substring(0, item.ItemDate.Ticks.ToString().Length - 10), // instead of .ToString("MM/dd/yyyy HH:mm") ,
    b = item.Description 
} into ItemGroup

You can play around by varying the number of digits subtracted from item.ItemDate.Ticks, substituting e.g. 10 with 9 or 8, etc.

@juharr well, i focused on the case he describes. My sample data are based on that case. — Giorgos Betsos, Dec 11 '14 at 13:21

score 0 · Answer 2 · answered Dec 11 '14 at 13:20

If you want left hand side to be a list then you can try this

var query = (from t in db.Table
             group t by new {t.Description, t.Date }
             into grp
             select new
             {
                 grp.Key.Description,
                 grp.Key.Date,                        
             }).ToList();

LINQ & Grouping data by a date range

2 Answers2

Linked