I am keeping staff clock-in times daily. Sometimes they work the next day without taking any break.

WHAT I NEED IS Without a break,the Total Hrs of staff who finish at midnight.

For example

In the above table, both staffs finished at midnight.

Staff A worked 15hrs without any breaks. But Staff B had a break between 9 PM and 10 Pm.

Expected result:

I tried for loop, but it failed because each staff has a different number of entries, codes got messy at the end.

I tried LINQ and joining the same table etc. but couldn't figure it out.

This website is my last option.

Thanks advance for help.

CodePudding user response：

Okay, this answer will need a bunch more work if you need this for work segments that cross dates or multiple dates. This assumes all work segments are in the same day.

The following groups all segments by staff, then adds up each segment total minutes. Then only returns the total if the last segment ends at midnight.

This test code returns the following:

Staff A: 15
Staff B: 20
Staff C: 0
Staff D: 2

Here's the code:

void Main()
{
    var segments = GetTestData();
    DateTime midnight = DateTime.Now.AddDays(1).Date;
    var results = segments
        .GroupBy(s => s.Name)
        .Select(grp => 
            new {
                Name = grp.Key, 
                Total = grp.Max(g => g.EndDate) == midnight 
                    ? grp.Sum(g => g.EndDate.Subtract(g.StartDate).TotalMinutes) 
                    : 0
            });

    foreach (var result in results)
    {
        Console.WriteLine($"{result.Name}: {result.Total/60F:#,##0}");
    }
}

class Segment
{
    public string Name { get; set; }
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }
}

Segment[] GetTestData()
{
    return new[]
    {
        new Segment { Name = "Staff A", StartDate = new DateTime(2023, 1, 18, 9 , 0, 0), EndDate = new DateTime(2023, 1, 18, 15, 0, 0) },
        new Segment { Name = "Staff A", StartDate = new DateTime(2023, 1, 18, 15, 0, 0), EndDate = new DateTime(2023, 1, 18, 22, 0, 0) },
        new Segment { Name = "Staff A", StartDate = new DateTime(2023, 1, 18, 22, 0, 0), EndDate = new DateTime(2023, 1, 19, 0 , 0, 0) },
        new Segment { Name = "Staff B", StartDate = new DateTime(2023, 1, 18, 3 , 0, 0), EndDate = new DateTime(2023, 1, 18, 21, 0, 0) },
        new Segment { Name = "Staff B", StartDate = new DateTime(2023, 1, 18, 22, 0, 0), EndDate = new DateTime(2023, 1, 19, 0 , 0, 0) },
        new Segment { Name = "Staff C", StartDate = new DateTime(2023, 1, 18, 3 , 0, 0), EndDate = new DateTime(2023, 1, 18, 21, 0, 0) },
        new Segment { Name = "Staff D", StartDate = new DateTime(2023, 1, 18, 22, 0, 0), EndDate = new DateTime(2023, 1, 19, 0 , 0, 0) }
    };
}