How to solve the SQL problem about the access system?

Question

SQL

Hello. Set a task for sql. I can not doperet as means only SQL to solve.

There is a pass system at the enterprise that saves only the pass ID and the time when it was used in the database. Employees can leave and enter during the working day (for example, smoke).
---- it is necessary to display the employee's worksheet by day. That is the hours worked. I have a problem with the condition that employees can go out and in during work. That is, the simplest option from the max time to subtract the minimum is not entirely correct.

Here is the table creation query:


Here is the table data:


Please help...

Answer 1

The task is not simple.
First you need to determine the event that was registered: entry or exit. This can be done by joining the table with itself by the condition "on the right are all the events of the current day that precede the event on the left". By counting the number of such events and assuming that the first event of the day is the input, you can determine the type of the current event by whether this number is even or odd.
Then you need to select pairs of "input" - "next output" and calculate the time difference between them. Again we use the left join with all subsequent events of the day of the desired type, taking the minimum.
Then sum up within a day.

Answer 2

The algorithm will be approximately the following:
1. You select all records of the use of a pass by an employee.
2. Group by date.
3. Sort in ascending order of time.
4. If we assume that the working day always starts with the entrance and ends with the exit, then we can calculate the periods of "working" time (subtract time 1 from time 2 entries, subtract 3 from 4, etc.).
5. You sum up the received periods.
To solve the problem, read about GROUP BY, ORDER BY

Answer 3

If we number the records for one employee during one day, then the odd ones are the inputs, the even ones are the outputs, respectively.
CTE, ROW_NUMBER().

Answer 4

As practice has shown: an accurate accounting of entrances and exits for each employee is obtained only at very secure enterprises where there is a herringbone turnstile where only one person can squeeze through and armed guards are watching this Christmas tree from both sides.
In all other cases, the situation "two exits, one entrance", etc. - quite a regular situation.
It makes the picture a little easier if there is a separation of entry and exit events ...
Well, and so - expanse for security officers, personnel officers and other watchmen:
for each employee, daytime events are classified into correct ones - the number of entries coincides with the number of exits and there are no combinations of entry-entry and exit- exit and incorrect
according to the correct ones, "labor discipline" is automatically considered, according to the incorrect ones - one of the watchmen sticks into the incorrect log, looks at the records, tortures the employee and, for example, deletes an extra entry due to double waving the card -> the log for the employee becomes correct ...
In general :
- first level selection of 100% correct employee-day samples
- auto-correction of obvious incorrectness of the type two inputs in a row within a minute
- manual correction of the rest
well, then sniff it with time sheets, then gaps-tolerances for being late up to 3 minutes, for example, variations in the admissibility of lunch break shifts and how exactly to interpret (violation or not) leaving 3 minutes early for lunch, but returning 15 minutes earlier and counting whether during working hours the fact that the employee came an hour earlier, and left an hour later and did not go to lunch))))))
And then all this will break down about the round-the-clock work of the enterprise where the day and the shift somewhat do not coincide)
ps without window functions such hard to count