How to extract maximum date from a list of random dates, grouped by week, month, quarter or year?

2
votes

Given a list of random dates, is there an elegant C# way (LINQ or otherwise) to extract the last date in the list, grouped by week, month, quarter or year?

I hope that's clear but if not, using this example set of dates:

  • Mon 01/01/2018
  • Tue 02/01/2018
  • Thu 04/01/2018
  • Tue 09/01/2018
  • Thu 11/01/2018
  • Fri 12/01/2018
  • Mon 22/01/2018
  • Tue 23/01/2018
  • Wed 24/01/2018
  • Wed 31/01/2018
  • Thu 01/02/2018
  • Tue 27/02/2018

Extracting maximum by week would yield

  • Thu 04/01/2018 = the last date in the first week in the sample
  • Fri 12/01/2018 = the last date in the second week in the sample
  • Wed 24/01/2018 = etc.
  • Thu 01/02/2018
  • Tue 27/02/2018

And extracting maximum by month would yield

  • Wed 31/01/2018 = the last date in January in the sample
  • Tue 27/02/2018 = the last date in February in the sample

I need to be able to do this extraction by week number, calendar month, calendar quarter and calendar year.

I'm not even sure how to begin this. So at present I have no code to share.

Any ideas will be very welcome.

CLARIFICATION: the weekday names are included here for the benefit of us humans. My actual date is proper DateTime types. So the code doesn't need to parse Strings to DateTimes.

Additionally, I can easily write something like this without LINQ. But what I am hoping for is an elegant solution using LINQ or some other clever trick.

2
c#linqdatecalendar
Even with LINQ, this will be quite complicated :( But not impossible :) – Christopher H.
This might give you an idea: rextester.com/JBA49267 – DxTx

2 Answers

1
votes

This is pretty straightforward using LINQ. Here the example for grouping by month:

var grouped = dates.OrderBy(x => x.Ticks).GroupBy(x => x.Month)
                   .Select(x => new {Month = x.Key, Max = x.Max()});

This gives for your example:

{ Month = 1, Max = 1/31/2018 12:00:00 AM }

{ Month = 2, Max = 2/27/2018 12:00:00 AM }

To do this for the week instead, use this lambda for the GroupBy:

x => CultureInfo.InvariantCulture.Calendar.GetWeekOfYear(
         x, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday
     )

Output:

{ Week = 1, Max = 1/4/2018 12:00:00 AM }

{ Week = 2, Max = 1/12/2018 12:00:00 AM }

{ Week = 4, Max = 1/24/2018 12:00:00 AM }

{ Week = 5, Max = 2/1/2018 12:00:00 AM }

{ Week = 9, Max = 2/27/2018 12:00:00 AM }

For the quarter:

x => (x.Month + 2)/3

Output:

{ Quarter = 1, Max = 2/27/2018 12:00:00 AM }

For the year:

x => x.Year

Output

{ Year = 2018, Max = 2/27/2018 12:00:00 AM }

0
votes

I settled on this in C# which can be adapted for any other grouping required

    public static Dictionary<Tuple<int, int>, DateTime> MaxByYearMonth(this List<DateTime> sourceDateTimes)
    {
        IEnumerable<DateTime>                             ordered = sourceDateTimes.OrderBy(x => x.Ticks);
        IEnumerable<IGrouping<Tuple<int, int>, DateTime>> grouped = ordered.GroupBy(x => new Tuple<int, int>(x.Year, x.Month));
        Dictionary<Tuple<int, int>, DateTime>             maxed   = grouped.ToDictionary(x => x.Key, x => x.Max());
        return maxed;
    }