1/. Find the range you are working on.
- Round Up the maximum date from your data to your define interval.
- Round Down the minimum from your data to your define interval.
public static DateTime RoundUp(DateTime dt, TimeSpan d)
=> new DateTime((dt.Ticks + d.Ticks - 1) / d.Ticks * d.Ticks, dt.Kind);
public static DateTime RoundDown(DateTime dt, TimeSpan d)
=> new DateTime(dt.Ticks - (dt.Ticks % d.Ticks), dt.Kind);
Edit:
With this RoundDown if your minimum is on a split time, no previous interval will be created.
E.g for 8:00, the minimum interval is {8:00-8:30} not {7:30-8:00}, {8:00-8:30}
Reference:
2/. Split the range into sub-range of your interval.
Example: from 8 to 9, in interval of 30min, is {{8-8:30}, {8:30-9}}
public static IEnumerable<Range<DateTime>> SplitDateRange(DateTime start, DateTime end, TimeSpan ChunkSize)
{
DateTime chunkEnd;
while ((chunkEnd = start.Add(ChunkSize)) < end)
{
yield return new Range<DateTime>(start, chunkEnd);
start = chunkEnd;
}
yield return new Range<DateTime>(start, end);
}
Using Range:
Range is a pretty basic thing, normally you have two variables (start/end) and write the same comparaison other and other. Here we abstract it away and apply it on class that implement IComparable
, so we can easly sort our range.
And provide 2 methods to know: If a object is in the range. Or if 2 ranges overlaps.
public struct Range<T> where T : IComparable<T>
{
public Range(T start, T end)
{
Start = start;
End = end;
}
public T Start { get; }
public T End { get; }
public bool Includes(T value)
=> Start.CompareTo(value) <= 0 && End.CompareTo(value) >= 0;
public bool Includes(Range<T> range)
=> Start.CompareTo(range.Start) <= 0 && End.CompareTo(range.End) >= 0;
}
Reference:
3/. "Multiply the value".
As you need split Value to be in two range:
8:30 is in range {8:00-8:30} and {8:30-9:00}
We will make a cartesian product of your date and the range and pair them based on of the date beeing in the range.
var temp = from i in input // For all i in input
from r in ranges // For all range
where r.Includes(i.Date) // If the input date is in a range
select new // We create a new entry
{
Range = r,
Field1 = i.Field1,
Field2 = i.Field2,
Field3 = i.Field3
};
4/. GroupBy
and Sum
.
Finaly a simple GroupBy
and Sum
var result =
temp .GroupBy(x => x.Range))
.Select(g =>
new
{
Range = g.Key,
SumField1 = g.Sum(x => x.Field1),
SumField2 = g.Sum(x => x.Field2),
SumField3 = g.Sum(x => x.Field3)
})
.ToList();
Reference:
live demo in this demo no extention class have been create in order to put everything in the main.