How to group by data and select first record from each group by datetime and group by again?-CodePudding

I have a table like below :

JobSchedular:

JobId     ProcessId      DepartmentId         Status         ProcessedTime    
1         100            10                   Done           2022-11-08 19:41
2         101            10                   Done           2022-11-09 19:41
3         100            10                   Failed         2022-11-09 19:41

I want to group by ProcessId and select top 1 Status by latest ProcessedTime(order by ProcessedTime desc) and then again group by "Status" to calculate "Done" and "Failed" statistics.

Final Output:

Done = 1
Failed = 1

Will consider "Fail" status for ProcessId = 100 based on latest 2022-11-09 19:41

Code:

var statistics = (from js in context.JobSchedular
                  where js.DepartmentId == 10
                  group by ProcessId //
                  select new StatisticsModel
                  {
                     Done = 1,
                     Failed = 1
                  }
                  ).FirstOrDefault();

I am little confused here with group by ProcessId and select top 1 Status by latest ProcessedTime and then further group by "Status" to calculate statistics.

Can someone please help?

CodePudding user response：

If I corectly understand expected result it should be the following query:

var jobs = 
    from js in context.JobSchedular
    where js.DepartmentId == 10
    select js;

var staitsticQuery = 
    from js in jobs
    group js by js.ProcessId into g
    select new 
    {
        ProcessId = g.Key,
        Done = g.Sum(x => x.Status == "Done" ? 1 : 0),
        Failed = g.Sum(x => x.Status == "Failed" ? 1 : 0)
    };

var finalQuery = 
    from s in staitsticQuery
    from js in jobs
        .Where(js => js.ProcessId == s.ProcessId)
        .OrderByDescending(js => js.ProcessedTime)
        .Take(1)
    select new 
    {
        ProcessId = s.ProcessId,
        Done = s.Done,
        Failed = s.Failed,
        LastStatus = js.Status
    };

var statistics = finalQuery.ToList();