Calculate time in List without using a loop

Is there a way to calculate the data on my List without using loop? My concern right now is it takes longer to process the 5000 ids of employee with different LogIn and LogOut using for loop in a week.

What i’ve done is the data is uploaded from csv to the database. Once all the data is uploaded i have a stored procedure call that will format the original data in EntryAndExit table like this.

My current code gets the first row and take the EntryTime “2021-02-07 00:00:00” and iterate from ExitTime the max calculated time is not suppose to be greater to 14 Hours. Each iteration i update the Flag column equals to “1” it means the ExitTime row is already calculated against EntryTime and it also my filter later which rows to delete in my list. As you can see this example data will loop through ExitTime field 40 times just to get the ExitTime “2021-02-07 07:08:06”. As you can see after that the calculation will be greater than 14 hours as it will jump to the next row ExitTime “2021-02-11 06:18:37”.

The goal is to get the 1st entryTime and EntryPoint then last ExitTime and ExitPoint. They have multiple random doors because we use our cards for entry and exit in all office doors.

My current code is this.

        int count = 0;
        DateTime entryTime = DateTime.Now;
        DateTime exitTime = DateTime.Now;
        TimeSpan difference = new TimeSpan();
        double maxHour = 0;
        DateTime exitTimeLastIndex = DateTime.Now;

        foreach (var empId in employeeNumberList)
        {
            var maxRow = listEntryAndExit.AsEnumerable().Where(f => f.EmployeeNumber == empId && f.Flag == null).Count();

            var query = listEntryAndExit.AsEnumerable().Where(f => f.EmployeeNumber == empId).OrderBy(f => f.EntryTime);

            while (count < maxRow)
            {
                while (count < maxRow)
                {
                    if (count == 0)
                    {
                        //get first row of entryTime field assign entryTime data to variable @entryTime once                                            
                        var queryEntryTime = listEntryAndExit.AsEnumerable().Where(f => f.EmployeeNumber == empId);
                        entryTime = queryEntryTime.AsEnumerable().OrderBy(x => x.EntryTime).Select(x => x.EntryTime).FirstOrDefault();
                    }


                    var queryExitTime = query.ElementAt(count);


                    if (queryExitTime != null)
                        difference = queryExitTime.ExitTime.Subtract(entryTime);

                    maxHour = difference.TotalHours;

                    if (maxHour > 14)
                    {
                        break;
                    }

                    if (maxHour < 14)
                    {

                        if (queryExitTime != null)
                            exitTimeLastIndex = queryExitTime.ExitTime;


                        var listUpdateEntryAndExit = listEntryAndExit.AsEnumerable().Where(f => f.EmployeeNumber == empId &&
                                                                f.ExitTime == exitTimeLastIndex).FirstOrDefault();

                        if (listUpdateEntryAndExit != null)
                        {
                            listUpdateEntryAndExit.Flag = 1;
                        }


                    }

                    count++;
                }

                count = 0;

                
                Console.WriteLine("Employee Number: " + empId + " | Entry Time: " + entryTime + " | Exit Time: " + exitTimeLastIndex);


                var deleteRows = listEntryAndExit.RemoveAll(f => f.Flag == 1);

                maxRow = listEntryAndExit.AsEnumerable().Where(x => x.EmployeeNumber == empId && x.Flag == null).Count();

The employeeNumberList holds the 5000 different employee ID numbers.

Answer

Databases are really good at this sort of thing.

You could do this all in sql, by using things such as lead to check the next row(s) letting you ensure there is not more than a 14 hour gap.

A simple example of this working looks something like:

with data as
(
select 
   employeenumber,
   entrytime,
   exittime,
   min(entrytime) over(partition by employeenumber order by entrytime) firstentry,
   lead(entrytime,1) over (partition by employeenumber order by entrytime) nextentry
from entryExitData
)
select distinct
    employeenumber,
    firstEntry,
    max(exittime) over (partition by employeenumber)
from
data
where datediff(hour,firstentry,nextentry)<=14

Where entryExitData is something like what you have already (with some fields omitted)

Live example where you can see some autogenerated data working: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=50d98249e11e3fcb51277cca5f3763e0

Advertisement

Answer