How to get the next 12 months with empty months

Question

I wanted to get the projected Balance for the next 12 months from the current month and if the Balance is empty for that month it will get the value from the nearest Month with a Balance of greater than 0.

void Main()
{
    var firstDayMonth = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
    var months = Enumerable.Range(0, 12)
                           .Select(m => new 
                            {
                                Month = firstDayMonth.AddMonths(m)
                            });

    List<SomeDate> SomeDates = new List<SomeDate>()
    {
        new SomeDate { Id = 1, Month = firstDayMonth.AddMonths(0), Balance = 1m },
        new SomeDate { Id = 2, Month = firstDayMonth.AddMonths(2), Balance = 1m },
        new SomeDate { Id = 3, Month = firstDayMonth.AddMonths(1), Balance = 6m },
        new SomeDate { Id = 4, Month = firstDayMonth.AddMonths(2), Balance = 5m },
        new SomeDate { Id = 5, Month = firstDayMonth.AddMonths(3), Balance = 3m },
        new SomeDate { Id = 6, Month = firstDayMonth.AddMonths(2), Balance = 2m },
        new SomeDate { Id = 7, Month = firstDayMonth.AddMonths(3), Balance = 4m },
        new SomeDate { Id = 8, Month = firstDayMonth.AddMonths(1), Balance = 2m },
        new SomeDate { Id = 9, Month = firstDayMonth.AddMonths(3), Balance = 3m },
    };

    var groupedMonths = SomeDates
                        .GroupBy(c => c.Month)
                        .Select(g => new 
                        {
                            Month = g.Key,
                            SumBalance = g.Sum(s => s.Balance)
                        });

    var Projected12MonthsBalance = from m in months
                                   join gm in groupedMonths on m.Month equals gm.Month into mm
                                   from gm in mm.DefaultIfEmpty()
                                   select new 
                                    {
                                        Month = m.Month, 
                                        Balance = gm == null ? 0m : gm.SumBalance
                                    };

    Console.WriteLine(Projected12MonthsBalance);
}

// Define other methods and classes here
public class SomeDate 
{
    public int Id { get; set; }
    public DateTime Month { get; set; }
    public decimal Balance { get; set; }
}

This is what I have tried so far. This would result to:

Month     | Balance
7/1/2015  | 1
8/1/2015  | 8
9/1/2015  | 8
10/1/2015 | 10
11/1/2015 | 0
...
6/1/2016  | 0

Is it possible to get a result somewhat like this:

Month     | Balance
7/1/2015  | 1
8/1/2015  | 8
9/1/2015  | 8
10/1/2015 | 10
11/1/2015 | 10 <-- gets the value from the nearest month with Balance greater than 0
...
6/1/2016  | 10 <-- gets the value from the nearest month with Balance greater than 0

I can't seem to complete the query so I've set zeroes for now. Any help would be much appreciated. Thanks!

Answer 1

This seems rather easy with LINQ, IMHO.

First, I modified months to avoid the use of the anonymous type (as it wasn't necessary).

var months =
    Enumerable
        .Range(0, 12)
        .Select(m => firstDayMonth.AddMonths(m));

Then I created a look-up for the balance figures by month:

var lookup =
    SomeDates
        .ToLookup(x => x.Month, x => x.Balance);

Look-ups are better that group-bys or dictionaries in that you can ask for values of a look-up where the key doesn't exist and it returns an empty list. It alleviates the need to check for the existence of the key before retrieving values.

I'm going to use .Aggregate(...) to build the final list, so I need to create the initial accumulator for the aggregate function:

var accumulator =
    months
        .Take(1)
        .Select(m => new { Month = m, Balance = lookup[m].Sum() })
        .ToList();

This is just the result of computing the balance for the first month and adding it to a list. This is a list of an anonymous type.

Now the final query is quite easy:

var Projected12MonthsBalance =
    months
        .Skip(1)
        .Aggregate(
            accumulator,
            (a, m) =>
            {
                var b = lookup[m].Sum();
                b = b == 0 ? a.Last().Balance : b;
                a.Add(new { Month = m, Balance = b });
                return a;
            });

This query skips the first month because we already added it to the accumulator. The lamdba function now simply computes the balance for each month and if it is zero it takes the balance from the proceeding month instead.

Here's the result I get:

Answer 2

As suggested above, you can do this more easily with a foreach loop. I would construct a dictionary containing totals for months, and where there is no entry for the month, I would use OrderBy to retrieve the 'closest' entry as you requested.

void main()
{
    var firstDayMonth = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
    var months = Enumerable.Range(0, 12)
                           .Select(firstDayMonth.AddMonths);

    List<SomeDate> SomeDates = new List<SomeDate>()
                                {
                                    new SomeDate { Id = 1, Month = firstDayMonth.AddMonths(0), Balance = 1m },
                                    new SomeDate { Id = 2, Month = firstDayMonth.AddMonths(2), Balance = 1m },
                                    new SomeDate { Id = 3, Month = firstDayMonth.AddMonths(1), Balance = 6m },
                                    new SomeDate { Id = 4, Month = firstDayMonth.AddMonths(2), Balance = 5m },
                                    new SomeDate { Id = 5, Month = firstDayMonth.AddMonths(3), Balance = 3m },
                                    new SomeDate { Id = 6, Month = firstDayMonth.AddMonths(2), Balance = 2m },
                                    new SomeDate { Id = 7, Month = firstDayMonth.AddMonths(3), Balance = 4m },
                                    new SomeDate { Id = 8, Month = firstDayMonth.AddMonths(1), Balance = 2m },
                                    new SomeDate { Id = 9, Month = firstDayMonth.AddMonths(3), Balance = 3m },
                                };

    var groupedMonths = SomeDates
                        .GroupBy(c => c.Month)
                        .ToDictionary(g => g.Key, g => g.Sum(s => s.Balance));

    var Projected12MonthsBalance = new List<Tuple<DateTime, decimal>>();
    foreach (var month in months)
    {
        decimal balance;
        if (groupedMonths.TryGetValue(month, out balance))
        {
            Projected12MonthsBalance.Add(new Tuple<DateTime, decimal>(month, balance));
        }
        else
        {
            Projected12MonthsBalance.Add(
                new Tuple<DateTime, decimal>(
                    month,
                    groupedMonths.OrderBy(g => g.Key.Subtract(month).Duration()).First().Value));
        }
    }

    foreach (var item in Projected12MonthsBalance)
    {
        Console.WriteLine("{0} {1}", item.Item1, item.Item2);
    }
}

EDIT:

If you prefer a purely linq solution, the following should work too:

var lookup = SomeDates.ToLookup(c => c.Month);

var Projected12MonthsBalance = months.Select(
    month => lookup.OrderBy(g => g.Key.Subtract(month).Duration())
                 .First())
                 .Select(g => new 
                        {
                            Month = g.Key, 
                            Balance = g.Sum(e => e.Balance)
                        });