How to chop a continuous date range list into a list of financial year in C#?

Question

Example: given a continuous list of date range

List[0] = from 2001 Jan 01 to 2001 Aug 14

List[1] = from 2001 Aug 15 to 2002 Jul 10

Let’s assume that a financial year is from 1st of July to 30th of June (of next year) so the output should be

AnotherList[0] = from 2000 Jul 01 to 2001 Jun 30

  period: 2001 Jan 01 to 2001 Jun 30

AnotherList[1] = from 2001 July 01 to 2002 Jun 30

  period: 2001 Jul 01 to 2001 Aug 14
  period: 2001 Aug 15 to 2002 Jun 30

AnotherList[2] = from 2002 July 01 to 2003 Jun 30

  period: 2002 Jul 01 to 2002 Jul 10

Again it's very easy to work out by hand but my method contains close to 100 lines of code with the combination of if else, for each and while loops which I think it's ugly. I am trying to simplify the algorithm so that it's easier to maintain and debug. Thanks in advance.

Answer 1

for each range in list // determine end of this fiscal year cut = new Date(range.start.year, 06, 31) if cut < range.start cut += year end

  if (range.end <= cut)
    // one fiscal year
    result.add range
    continue
  end

  result.add new Range(range.start, cut)

  // chop off whole fiscal years
  start = cut + day
  while (start + year <= range.end)
    result.add new Range(start, start + year - day)
    start += year
  end

  result.add new Range(start, range.end)
end

Sorry for mix of ruby and java :)

Answer 2

EDIT: Changed to include clearer requirements.

Given a list that contains contiguous date ranges, the code doesn't have to be hard at all. In fact, you don't even have to write an actual loop:

public const int FYBeginMonth = 7, FYBeginDay = 1;

public static int FiscalYearFromDate(DateTime date)
{
    return date.Month > FYBeginMonth ||
           date.Month == FYBeginMonth && date.Day >= FYBeginDay ?
        date.Year : date.Year - 1;
}

public static IEnumerable<DateRangeWithPeriods>
              FiscalYears(IEnumerable<DateRange> continuousDates)
{
    int startYear = FiscalYearFromDate(continuousDates.First().Begin),
        endYear = FiscalYearFromDate(continuousDates.Last().End);
    return from year in Enumerable.Range(startYear, endYear - startYear + 1)
           select new DateRangeWithPeriods {
               Range = new DateRange { Begin = FiscalYearBegin(year),
                                       End = FiscalYearEnd(year) },
      // start with the periods that began the previous FY and end in this FY
               Periods = (from range in continuousDates
                          where FiscalYearFromDate(range.Begin) < year
                             && FiscalYearFromDate(range.End) == year
                          select new DateRange { Begin = FiscalYearBegin(year),
                                                 End = range.End })
                          // add the periods that begin this FY
                  .Concat(from range in continuousDates
                          where FiscalYearFromDate(range.Begin) == year
                          select new DateRange { Begin = range.Begin,
                                 End = Min(range.End, FiscalYearEnd(year)) })
                          // add the periods that completely span this FY
                  .Concat(from range in continuousDates
                          where FiscalYearFromDate(range.Begin) < year
                             && FiscalYearFromDate(range.End) > year
                          select new DateRange { Begin = FiscalYearBegin(year),
                                                 End = FiscalYearEnd(year) })

           };
}

This assumes some DateRange structures and helper functions, like this:

public struct DateRange
{
    public DateTime Begin { get; set; }
    public DateTime End { get; set; }
}

public class DateRangeWithPeriods
{
    public DateRange Range { get; set; }
    public IEnumerable<DateRange> Periods { get; set; }
}
private static DateTime Min(DateTime a, DateTime b)
{
    return a < b ? a : b;
}

public static DateTime FiscalYearBegin(int year)
{
    return new DateTime(year, FYBeginMonth, FYBeginDay);
}

public static DateTime FiscalYearEnd(int year)
{
    return new DateTime(year + 1, FYBeginMonth, FYBeginDay).AddDays(-1);
}

This test code:

static void Main()
{
    foreach (var x in FiscalYears(new DateRange[] { 
        new DateRange { Begin = new DateTime(2001, 1, 1),
                        End = new DateTime(2001, 8, 14) },
        new DateRange { Begin = new DateTime(2001, 8, 15),
                        End = new DateTime(2002, 7, 10) } }))
    {
        Console.WriteLine("from {0:yyyy MMM dd} to {1:yyyy MMM dd}",
                          x.Range.Begin, x.Range.End);
        foreach (var p in x.Periods)
            Console.WriteLine(
            "    period: {0:yyyy MMM dd} to {1:yyyy MMM dd}", p.Begin, p.End);
    }
}

outputs:

from 2000 Jul 01 to 2001 Jun 30
    period: 2001 Jan 01 to 2001 Jun 30
from 2001 Jul 01 to 2002 Jun 30
    period: 2001 Jul 01 to 2001 Aug 14
    period: 2001 Aug 15 to 2002 Jun 30
from 2002 Jul 01 to 2003 Jun 30
    period: 2002 Jul 01 to 2002 Jul 10

Answer 3

You can be clever with GroupBy

// Beginning of earliest financial year
var start = new DateTime(2000,7,1); 
var range = Enumerable.Range(0,365*2);

// Some random test data
var dates1 = range.Select(i => new DateTime(2001,1,1).AddDays(i) );
var dates2 = range.Select(i => new DateTime(2003,1,1).AddDays(i) );

// Group by distance in years from beginning of earliest financial year
var finYears =
    dates1
    .Concat(dates2)
    .GroupBy(d => d.Subtract(start).Days / 365 );

This gives an IEnumerable<IGrouping<int, DateTime>> with each outer enumerable containing all the dates in the 2 lists in a single financial year.