CODEWITHSUNDEEP
c#linq
DK ALT
DK ALT

Reputation: 2193

LINQ Select last day when certain condition was met

I've got a list of objects like this

MyObject
  .StartDate
  .EndDate
  .Number

I need to know when was the last day the sum of numbers was above a certain number. The number will only enter the sum for the day if it's between StartDate and EndDate.

var list = List<MyObject>();

from list
  where (list.Sum(x => x.Number) > 100)
  select lastdate

Is this possible with LINQ?

Upvotes: 0

Views: 318

Answers (4)

RB84
RB84

Reputation: 341

        var lastdate = list.OrderByDescending(l => l.EndDate)
         .First(l =>
             list.Where(s => s.StartDate <= l.EndDate && l.EndDate <= s.EndDate)
             .Sum(s => s.Number) > 100).EndDate;

We do not need to check any date, because the change to sum is dictated by changes to enddate.

So:

  • Sort the list by descending enddate order, because we only need the last date.
  • Search the first hit for which the sum of numbers is higher then 100:
    • Create a list of all the items which have been started before and ended or on after the enddate
    • Sum all the numbers

This way unnecesarry iterations are prevented and items which have ended later but also started later then the date you are checking, are ignored.

Upvotes: 0

D Stanley
D Stanley

Reputation: 152626

assuming the list is in order:

var list = List<MyObject>();

var lastDate = list.First(item => list.Where(l => l.EndDate <= item.EndDate)
                                      .Sum(x => x.Number) > 100
                         )
                   .EndDate

if not just add an order:

var list = List<MyObject>();

var lastDate = list.OrderBy(item => item.EndDate)
                   .First(item => list.Where(l => l.EndDate <= item.EndDate)
                                      .Sum(x => x.Number) > 100
                         )
                   .EndDate

However you're re-scanning the loop for each item. It will be more efficient with a simple loop:

DateTime endDate;
int total;
foreach(var item in list.OrderBy(item => item.EndDate))
{
   total += item.Number;
   if(total > 100)
   {
      endDate = item.EndDate;
      break;
   }
}

Upvotes: 4

Maxim Zhukov
Maxim Zhukov

Reputation: 10140

var result = items.Where(t => items.SkipWhile(tt => tt.EndDate != t.EndDate)
    .Sum(tt => tt.Number) > 100).First().EndDate;

Upvotes: 1

akousmata
akousmata

Reputation: 1043

You may need an additional property to do this. Not sure how to do it in a one line LINQ statement:

public class MyObjectWithSum
{
    public DateTime StartDate {get;set;}
    public DateTime EndDate {get;set;}
    public int Number {get;set;}
    public int SumAsOfEndDate {get;set;}
}

var list = new List<MyObjectWithSum>();

foreach(var item in list)
{
    item.SumAsOfEndDate = list.Sum(x => x.Number).Where(y => y.EndDate <= item.EndDate);
}

list.OrderBy(x => x.EndDate).First(x => x.SumAsOfEndDate > 100).EndDate;

Upvotes: 1

Related Questions