Reputation: 406

Group By timespan in LINQ

Let's suppose we have a class like:

    class Forecast
    {
      int LocationId;
      int DepartmentId;
      DateTime StartDate;
      DateTime EndDate;
      int CountOfEmployees;
    }

I have a list of Forecasts: List<Forecasts> forecasts

The list is grouped by a 15 minute interval, for example:

forecast[0] = new Forecast { LocationId = 1, DepartmentId = 1, StartTime = 2018-10-01 06:00:00.000, EndTime = 2018-10-01 06:15:00.000, CountOfEmployees = 2  }
forecast[1] = new Forecast { LocationId = 1, DepartmentId = 1, StartTime = 2018-10-01 06:15:00.000, EndTime = 2018-10-01 06:30:00.000, CountOfEmployees = 1  }
forecast[2] = new Forecast { LocationId = 1, DepartmentId = 1, StartTime = 2018-10-01 06:30:00.000, EndTime = 2018-10-01 06:45:00.000, CountOfEmployees = 3  }
forecast[3] = new Forecast { LocationId = 1, DepartmentId = 1, StartTime = 2018-10-01 06:45:00.000, EndTime = 2018-10-01 07:00:00.000, CountOfEmployees = 1  }
forecast[4] = new Forecast { LocationId = 1, DepartmentId = 1, StartTime = 2018-10-01 07:00:00.000, EndTime = 2018-10-01 07:15:00.000, CountOfEmployees = 2  }
forecast[5] = new Forecast { LocationId = 1, DepartmentId = 1, StartTime = 2018-10-01 07:15:00.000, EndTime = 2018-10-01 07:30:00.000, CountOfEmployees = 2  }
forecast[6] = new Forecast { LocationId = 1, DepartmentId = 1, StartTime = 2018-10-01 07:30:00.000, EndTime = 2018-10-01 07:45:00.000, CountOfEmployees = 5 }
forecast[7] = new Forecast { LocationId = 1, DepartmentId = 1, StartTime = 2018-10-01 07:45:00.000, EndTime = 2018-10-01 08:00:00.000, CountOfEmployees = 3  }
forecast[8] = new Forecast { LocationId = 2, DepartmentId = 2, StartTime = 2018-10-01 06:00:00.000, EndTime = 2018-10-01 06:15:00.000, CountOfEmployees = 2  }
forecast[9] = new Forecast { LocationId = 2, DepartmentId = 2, StartTime = 2018-10-01 06:15:00.000, EndTime = 2018-10-01 06:30:00.000, CountOfEmployees = 1  }
forecast[10] = new Forecast { LocationId = 2, DepartmentId = 2, StartTime = 2018-10-01 06:30:00.000, EndTime = 2018-10-01 06:45:00.000, CountOfEmployees = 3  }
forecast[11] = new Forecast { LocationId = 2, DepartmentId = 2, StartTime = 2018-10-01 06:45:00.000, EndTime = 2018-10-01 07:00:00.000, CountOfEmployees = 1  }
forecast[12] = new Forecast { LocationId = 2, DepartmentId = 2, StartTime = 2018-10-01 07:00:00.000, EndTime = 2018-10-01 07:15:00.000, CountOfEmployees = 2  }
forecast[13] = new Forecast { LocationId = 2, DepartmentId = 2, StartTime = 2018-10-01 07:15:00.000, EndTime = 2018-10-01 07:30:00.000, CountOfEmployees = 2  }
forecast[14] = new Forecast { LocationId = 2, DepartmentId = 2, StartTime = 2018-10-01 07:30:00.000, EndTime = 2018-10-01 07:45:00.000, CountOfEmployees = 5 }
forecast[15] = new Forecast { LocationId = 2, DepartmentId = 2, StartTime = 2018-10-01 07:45:00.000, EndTime = 2018-10-01 08:00:00.000, CountOfEmployees = 3  }

I would want the result to be grouped in a time interval of 60 minutes, Location and Department along with summation of countOfEmployees. So the expected result should be something like this:

result[0] = new Forecast { LocationId = 1, DepartmentId = 1, StartTime = 2018-10-01 06:00:00.000, EndTime = 2018-10-01 07:00:00.000, CountOfEmployees = 7  }
result[1] = new Forecast { LocationId = 1, DepartmentId = 1, StartTime = 2018-10-01 07:00:00.000, EndTime = 2018-10-01 08:00:00.000, CountOfEmployees = 12  }
result[2] = new Forecast { LocationId = 2, DepartmentId = 2, StartTime = 2018-10-01 07:00:00.000, EndTime = 2018-10-01 08:00:00.000, CountOfEmployees = 12  }
result[3] = new Forecast { LocationId = 2, DepartmentId = 2, StartTime = 2018-10-01 07:00:00.000, EndTime = 2018-10-01 08:00:00.000, CountOfEmployees = 12  }

Can someone please point me in the right direction ?

Upvotes: 1

Answers (4)

SᴇM

Reputation: 7213

You can remove minutes from times, then group by new StartTime:

var result = forecast
.GroupBy(g => new { StartTime = g.StartTime.Date.AddHours(g.StartTime.Hour), g.LocationId, g.DepartmentId })
.Select(s => 
    new Forecast()
    {
        LocationId = s.Key.LocationId,
        DepartmentId = s.Key.DepartmentId,
        StartTime = s.Key.StartTime,
        EndTime = s.Max(m => m.EndTime),
        CountOfEmployees = s.Sum(c => c.CountOfEmployees),
    }
)
.OrderBy(o => o.LocationId)
.ThenBy(o => o.DepartmentId)
.ThenBy(o => o.StartTime)
.ToList();

Or with query syntax:

var q = from f in forecast
        group f by new 
        { 
            StartTime = f.StartTime.Date.AddHours(f.StartTime.Hour), 
            f.LocationId, 
            f.DepartmentId 
        } into g
        orderby g.Key.LocationId, g.Key.DepartmentId, g.Key.StartTime
        select new Forecast
        {
            LocationId = g.Key.LocationId,
            DepartmentId = g.Key.DepartmentId,
            StartTime = g.Key.StartTime,
            EndTime = g.Max(m => m.EndTime),
            CountOfEmployees = g.Sum(s => s.CountOfEmployees)
        };

output:

LocationId: 1, DepartmentId: 1, StartTime: 10/1/2018 6:00:00, EndTime: 10/1/2018 7:00:00, CountOfEmployees: 7
LocationId: 1, DepartmentId: 1, StartTime: 10/1/2018 7:00:00, EndTime: 10/1/2018 8:00:00, CountOfEmployees: 12
LocationId: 2, DepartmentId: 2, StartTime: 10/1/2018 6:00:00, EndTime: 10/1/2018 7:00:00, CountOfEmployees: 7
LocationId: 2, DepartmentId: 2, StartTime: 10/1/2018 7:00:00, EndTime: 10/1/2018 8:00:00, CountOfEmployees: 12

DotNetFiddle Example

Upvotes: 0

Anu Viswan

Reputation: 18155

You could do something similar, assuming that your interval will always start at 00 mins to 60 mins, and not from say, 6:15 to 7:15.

var result = forecast.GroupBy(x=>new {x.StartTime.Hour, x.LocationId, x.DepartmentId})
                    .Select(x=> new Forecast
                                { 
                                    LocationId = x.Key.LocationId, 
                                    DepartmentId = x.Key.DepartmentId, 
                                    StartTime =  x.ToList().OrderBy(e=>e.StartTime).First().StartTime,
                                    EndTime =  x.ToList().OrderBy(e=>e.EndTime).Last().EndTime,
                                    CountOfEmployees = x.ToList().Sum(c=>c.CountOfEmployees) 
                                    });

Upvotes: 1

Ashokan Sivapragasam

Reputation: 2189

This should work. As @SeM mentioned, you need to show your research. Your question did not show any work.

var results = from foreCastSelector in foreCasts
          group foreCastSelector by new
          {
              LocationId = foreCastSelector.LocationId,
              DepartmentId = foreCastSelector.DepartmentId,
              StartDate = foreCastSelector.StartDate.AddMinutes(-1 * foreCastSelector.StartDate.Minute),
              EndDate = foreCastSelector.StartDate.AddMinutes(-1 * foreCastSelector.StartDate.Minute).AddHours(1)
          } into gcs
          select new Forecast
          {
              LocationId = gcs.Key.LocationId,
              DepartmentId = gcs.Key.DepartmentId,
              StartDate = gcs.Key.StartDate,
              EndDate = gcs.Key.EndDate,
              CountOfEmployees = gcs.Sum(r => r.CountOfEmployees)
              };

foreach (var result in results)
{
    Console.WriteLine(result.LocationId + " | " + result.DepartmentId + " | "
        + result.StartDate.ToString() + " | "
       + result.EndDate.ToString() + " | "
       + result.CountOfEmployees);
}

Upvotes: 0

D-Shih

Reputation: 46219

You can try to use linq select with group by

Establish a time grouping in linq GroupBy for your logic for EndTime, StartTime, LocationId

StartTime and EndTime for each hour.

var result = forecast
.GroupBy(_ => new {
    _.LocationId,
    StartTime = new DateTime(_.StartTime.Year,
                              _.StartTime.Month,
                              _.StartTime.Day,
                              _.StartTime.Hour,0,0,0),
    EndTime = new DateTime(_.StartTime.Year,
                              _.StartTime.Month,
                              _.StartTime.Day,
                              _.StartTime.Hour + 1, 0, 0, 0)
})
.Select(x => new {
    x.Key.LocationId,
    x.Key.StartTime,
    x.Key.EndTime,
    CountOfEmployees = x.Sum(y=>y.CountOfEmployees)});

c# online

Result

LocationId : 1 StartTime : 10/1/2018 6:00:00 AM EndTime : 10/1/2018 7:00:00 AM  CountOfEmployees : 7
LocationId : 1 StartTime : 10/1/2018 7:00:00 AM EndTime : 10/1/2018 8:00:00 AM  CountOfEmployees : 12
LocationId : 2 StartTime : 10/1/2018 6:00:00 AM EndTime : 10/1/2018 7:00:00 AM  CountOfEmployees : 7
LocationId : 2 StartTime : 10/1/2018 7:00:00 AM EndTime : 10/1/2018 8:00:00 AM  CountOfEmployees : 12

Upvotes: 3

Group By timespan in LINQ

Answers (4)

Related Questions