时间戳

首页 > 解决方案 > 如何将linq c#中日期之间的期间相加到特定期间

c# - 如何将linq c#中日期之间的期间相加到特定期间

问题描述

我有一个包含三列的数据表，我需要通过 LINQ 查询减去和求和差值Date1来检查每个员工 ID 何时达到两年或将达到两年。Date2

如果Date2value 为 null，则表示ID到目前为止仍在工作。

   ID                    Date1                                 Date2
>  100              10/01/2016                               09/01/2017               
>  100              20/09/2017                               25/05/2019  
>  101              05/07/2018

我需要如下输出：

   ID                two_years
> 100                    19/09/2018   
> 101                    04/07/2020

标签： c#linqdatesum

解决方案

Using my Scan extension which is based on the APL Scan operator (like Aggregate, only it returns the intermediate results):

public static class IEnumerableExt {
    // TRes seedFn(T FirstValue)
    // TRes combineFn(TRes PrevResult, T CurValue)
    public static IEnumerable<TRes> Scan<T, TRes>(this IEnumerable<T> src, Func<T, TRes> seedFn, Func<TRes, T, TRes> combineFn) {
        using (var srce = src.GetEnumerator()) {
            if (srce.MoveNext()) {
                var prev = seedFn(srce.Current);

                while (srce.MoveNext()) {
                    yield return prev;
                    prev = combineFn(prev, srce.Current);
                }
                yield return prev;
            }
        }
    }
}

Then assuming by two years, you mean 2 * 365 days, and assuming you count the beginning and ending dates of each period as part of the total, this LINQ will find the answer:

var ans = src.Select(s => new { s.ID, s.Date1, s.Date2, Diff = (s.Date2.HasValue ? s.Date2.Value-s.Date1 : DateTime.Now.Date-s.Date1).TotalDays+1 })
             .GroupBy(s => s.ID)
             .Select(sg => new { ID = sg.Key, sg = sg.Scan(s => new { s.Date1, s.Date2, s.Diff, DiffAccum = s.Diff }, (res, s) => new { s.Date1, s.Date2, s.Diff, DiffAccum = res.DiffAccum + s.Diff }) })
             .Select(IDsg => new { IDsg.ID, two_year_base = IDsg.sg.FirstOrDefault(s => s.DiffAccum > twoYears) ?? IDsg.sg.Last() })
             .Select(s => new { s.ID, two_years = s.two_year_base.Date1.AddDays(twoYears-(s.two_year_base.DiffAccum - s.two_year_base.Diff)).Date });

If your original data is not sorted in Date1 order, or ID+Date1 order, you will need to add OrderBy to sort by Date1.

Explanation: First we compute the days worked(?) represented by each row, using today if we don't have an ending Date2. Then group by ID and work on each group. For each ID, we compute the running sum of days worked. Next find the Date1 that proceeds the two year mark using the running sum (DiffAccum) and compute the two year date from Date1 and the remaining time needed in that period.

If it is possible a particular ID could have a lot of periods, you could use another variation of Scan, ScanUntil which short circuits evaluation based on a predicate.

推荐阅读