Linq: Group by date intervals (5, 10, 15, etc.. years)

Question

I have an MVC/ASP.NET web application that tracks employee information such as StartDate, which is a DateTime datatype, that represents when a specific employee started at the company.

We also give out awards for employees for time at the company, and would like to group by 5, 10, 15, 20, 25, 30+ years at the company.

How can I write a Linq statement that would group by only the employees (people) whose time at company has hit one of the year interval benchmarks (5, 10, 15, etc...) that will be receiving a "time at company" award? Thanks for any help.

You need to show what you have tried, provide some example code. And have you searched for an answer as I'm fairly sure this has been asked before? — DavidG, Aug 12 '17 at 01:52
@DavidG - I've been looking around SO and haven't found a solution that works. The ones I've found are either specifically for SQL (and having trouble converting to linq) or aren't for static intervals previously set up. The only sample code I have is where I'm querying the data context to get people who are active within the company (!EndDate.HasValue), and grouping by the StartDate. I've done plenty of grouping before, just not with set data like this before. — Element808, Aug 12 '17 at 01:59
@Nkosi - sorry mine is slightly different because I want an exclusive query for only those people that fit specifically into that year interval. Not ALL people. — Element808, Aug 12 '17 at 02:05
The first filter to narrow the group set would be to those whose start date is older than 5 years and then start grouping by your interval — Nkosi, Aug 12 '17 at 02:12
So would I have to create a new linq query for each interval? I'm missing how the interval gets set... — Element808, Aug 12 '17 at 02:13
I read the duplicate post link you supplied, it's only filters on the last 5 minutes of whatever datetime gets passed to it. That is not the same as my question...if I have to create a linq query for each interval, it seems like a bit of overkill. Maybe I should have worded my question like that. — Element808, Aug 12 '17 at 02:15
No. You basically want to group the duration in 5 year increments then calculate number of years per person, and divide it by 5. You group by that value and you have your groups. — Nkosi, Aug 12 '17 at 02:17

score 1 · Accepted Answer · answered Aug 12 '17 at 03:18

Review the following Unit tests used to demonstrate how to group by periods/intervals.

[TestClass]
public class GroupByDateIntervalsTests {
    [TestMethod]
    public void Group_By_5_year_Intervals_Max_30() {
        var employees = GenerateRandomDates(DateTime.Now, 5, 40, 50).Select((d, i) => new { id = i, StartDate = d });

        var now = DateTime.Today;
        var period = 5;
        var maxPeriod = 30;
        var groups = from employee in employees
                     let interval = DateTime.MinValue.AddDays((now - employee.StartDate).TotalDays).Year / period
                     group employee by Math.Min(interval * period, maxPeriod) into g
                     orderby g.Key
                     select new {
                         period = g.Key,
                         employees = g.Select(e => e.id).ToArray()
                     };

        var result = groups.ToList();
    }

    [TestMethod]
    public void Group_By_Random_Interval_Max_30() {
        var employees = GenerateRandomDates(DateTime.Now, 5, 40, 50).Select((d, i) => new { id = i, StartDate = d });

        var now = DateTime.Today;
        var periods = new[] { 5, 10, 20, 30 };
        var groups = employees
            .GroupBy(employee => {
                var period = DateTime.MinValue.AddDays((now - employee.StartDate).TotalDays).Year;
                var interval = periods.Where(p => (period / p) > 0).Max();
                return Math.Min(interval, periods.Max());
            })
            .Select(g => new {
                period = g.Key,
                employees = g.Select(e => e.id).ToArray()
            });

        var result = groups.ToList();
    }

    public List<DateTime> GenerateRandomDates(DateTime rootDate, int minAgeAtRootDate, int maxAgeAtRootDate, int count) {
        Contract.Assert(minAgeAtRootDate <= maxAgeAtRootDate, "invalid age range. Minimum age cannot be higher than maximum age");
        var minDate = rootDate.Date.AddYears(-maxAgeAtRootDate);
        var maxDate = rootDate.Date.AddYears(-minAgeAtRootDate);
        var range = (maxDate - minDate).Days;
        if (range == 0) {
            range = 364;
        }
        var random = new Random();
        var dates = Enumerable
            .Range(1, count)
            .Select(i => minDate.AddDays(random.Next(range)))
            .ToList();
        return dates;
    }
}

score 1 · Answer 2 · edited Aug 12 '17 at 12:28

Great job with this solution! With your solution, I took your main logic and I made a few changes, substantial enough that I thought others might benefit from them. Definitely wouldn't have been able to do it with your patience in helping and your expertise with what I was trying to accomplish.

See below for my changes...

Controller

public ActionResult LengthOfService()
{
    ViewBag.Title = "Length of Service Awards Report";
    var people = db.People.ToList().Where(p => !p.EndDate.HasValue);
    var now = DateTime.Today;
    var period = 5;
    var maxPeriod = 30;
    var query = (from p in people
                let interval = DateTime.MinValue.AddDays((now - p.LengthOfService).TotalDays).Year / period
                where interval > 0
                group p by Math.Min(interval * period, maxPeriod) into x
                orderby x.Key
                select new AwardInfo
                {
                    Years = x.Key,
                    People = x
                }).ToList(); 
    return View(query);
}

View Model

public class AwardInfo
{
    public int Years { get; set; }
    public IEnumerable<People> People { get; set; }
}

View

@model List<CPR.Models.AwardInfo>

<h2>@ViewBag.Title</h2>

<table class="table table-responsive table-hover">
    <thead>
        <tr>

            <th class="col-sm-1">Service Award</th>
            <th class="col-sm-2">Name</th>
            <th class="col-sm-2">Date of Service</th>
            <th class="col-sm-2">Company</th>
        </tr>
    </thead>
    @foreach (var item in Model)
    {
        <tbody>
            <tr>
                <th class="panel-bg" colspan="5">@item.Years years</th>
            </tr>
        </tbody>
        <tbody>
            @foreach (var person in item.People)
            {
                <tr>
                    <td class="col-sm-1"></td>
                    <td class="col-sm-2">@person.LastName, @person.FirstName</td>
                    <td class="col-sm-2">@person.LengthOfService.ToShortDateString()</td>
                    <td class="col-sm-2">@person.Companies.Name</td>
                </tr>
            }
        </tbody>
    }
</table>

Linq: Group by date intervals (5, 10, 15, etc.. years)

2 Answers2