How to get the next 12 months with empty months

Question

I wanted to get the projected Balance within the next 12 months from the current month, and if Balance empty for this month, it will get the value from the nearest Month with Balance 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 will lead 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 the result something 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 cannot complete the request, so I set the zeros at the moment. Any help is appreciated. Thanks!

Answer 1

It looks pretty easy with LINQ, IMHO.

Firstly, I modified months to avoid using an anonymous type (since this was not necessary).

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

Then I created a list of balances by month:

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

Views are better than group or dictionaries in which you can query for search values ​​where the key does not exist and it returns an empty list. This makes it easier to check for a key before retrieving the values.

I am going to use .Aggregate(...) to create 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 the result of calculating the balance during the first month and adding it to the list. This is a list of anonymous type.

Now the final request is pretty simple:

 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 request skips the first month because we have already added it to the battery. Now the lamdba function simply calculates the balance for each month, and if it is zero, instead it takes the balance from the next month.

Here is the result:

Answer 2

As suggested above, you can do this more easily with the foreach . I would build a dictionary containing the totals for several months, and where there is no record for the month, I would use OrderBy to get the closest record of your choice.

 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 pure linq solution, the following should work:

 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) });