15,302,644 members
1.00/5 (1 vote)
See more:
Dear All,

Let me explain the scenario. "ABC" company has finger print access and attendance system, which logs all punches(whether in or out), device is not that intelligent to determine either its out punch or in punch, it simply records all the punches.

"ABC" company works in 4 Shifts
```SHIFT STARTTIME ENDTIME
A     6:00      14:00
B     14:00     22:00
C     22:00     6:00
G     9:00      6:00```

Employees who works on A/B/C shift can continuously work on 2 shifts(C-Off(paid leave) or a day off is given for such cases)

An employee who is working on A shift can also attend B shift based on the requirement but its must to punch out for A shift and again Punch in for B shift(15 minute interval between these 2 punches is mandatory rule so that system can understand that he is present on the 2nd shift also)

Based on the above working system at the company following is the punch log of the employee(emp code 3)

```EMP 	LOGDATETIME
3	01-11-2017 14:00
3	01-11-2017 14:10
3	01-11-2017 15:01
3	01-11-2017 16:01
3	01-11-2017 17:11
3	01-11-2017 22:00
3	01-11-2017 22:15
3	01-11-2017 23:15
3	02-11-2017 01:15
3	02-11-2017 06:10
3	02-11-2017 22:01
3	02-11-2017 22:15
3	02-11-2017 23:15
3	03-11-2017 01:15
3	03-11-2017 06:10
3	05-11-2017 06:00
3	05-11-2017 07:10
3	05-11-2017 08:10
3	05-11-2017 09:10
3	05-11-2017 12:10
3	05-11-2017 14:01
3	06-11-2017 14:01
3	06-11-2017 15:01
3	06-11-2017 15:01
3	06-11-2017 22:01```

Now as you can see it has several punches in log I need to calculate and show the data like following:

```EMPCODE       IN                  OUT                 SHIFT
3	      01-11-2017 14:00	01-11-2017 22:00       B
3	      01-11-2017 22:15  02-11-2017 06:10       C
3	      02-11-2017 22:01  03-11-2017 06:10       C
3	      05-11-2017 06:00  05-11-2017 14:01       A
3         06-11-2017 14:01  05-11-2017 22:01       B(HALF DAY)```

Rules to be considered are as follows
1) Employees are allowed to come early 30 minute before their shift
2) Employees are allowed to go early if they came early and their working hr has completed 8hr
3) 3 Short punches(means employees can leave 40 minutes before their shift end) every month.
4) employee can leave late based on the requirement, it will be considered current shift, but overtime will be calculated.

What I have tried:

I have read several SAP/.NET solution documents but none cover the above requirement, nor give me any ideas how to proceed. Most company has different punch device for In and Out. but in this case a single device is responsible for logging IN/OUT, thats making things more complicated.

I am trying to get a full proof logic that can handle such case.

Any help/ideas/document/pseudo code will be highly appreciated.
Posted
Updated 20-Dec-17 19:49pm
Richard MacCutchan 2-Dec-17 4:26am

The issue really is to know when the first punch occurs. Taking that as a 'punch in', you just need to alternate time stamps from then as in (first), out, in, out ...
RaviSNaik 3-Dec-17 23:58pm

but the real problem is separating them as Shift,and multiple shift on a single day.
Richard MacCutchan 4-Dec-17 4:55am

Yes, but only you and the factory management can figure out how you allocate the times to different shifts.
RaviSNaik 6-Dec-17 7:21am

already allocated, read the start of the post, i think you are completely misunderstanding the entire topic.

## Solution 1

This problem falls into the realm of what I believe is generally called "island and gap analysis".

If this is a real-world system you're working on (not a study project), be aware that this is a hard problem even for experts, let alone someone who is having to ask where to begin.

Your bosses should be warned that there is no practicable solution which is fully automatic (only a partial solution that deals with unambiguous cases, and then flags unusual/exceptional cases for human review).

Also, if the EMP column refers to an individual Employee Id, be aware that in the example you've given, Employee 3 is shown as doing two shifts back-to-back over 1/11/2017 and 2/11/2017.

If that is correct, then in all likelihood you will not be able to solve this problem at all, because the implication of some workers doing a 16-hour shift is that you can't rely on the rest period being the larger part of the day (and that frustrates the identification of the "islands and gaps").

Here's an example of how you'd begin to tackle the problem in SQL, assuming there was a table called "punch_log" with two columns "emp" and "logdatetime".

SQL
```WITH

step1 AS
(
SELECT
emp
,ROW_NUMBER() OVER (PARTITION BY emp ORDER BY logdatetime) AS employee_punch_num
,logdatetime
,LEAD(logdatetime) OVER (PARTITION BY emp ORDER BY logdatetime) AS next_logdatetime

FROM
punch_log
)

,step2 AS
(
SELECT
*
,CAST(DATEDIFF(MINUTE, logdatetime, next_logdatetime) AS FLOAT)/60 AS length_between_punches

FROM
step1
)

,step3 AS
(
SELECT
*
,IIF(length_between_punches IS NULL, NULL, IIF(length_between_punches > 12 /*ASSUMING ANY TIME DIFFERENCE BETWEEN PUNCHES OVER 12 HOURS IS REST*/, 'REST', 'WORK')) AS work_rest_ind

FROM
step2
)

,step4 AS
(
SELECT
*
,employee_punch_num - ROW_NUMBER() OVER (PARTITION BY emp, work_rest_ind ORDER BY emp, employee_punch_num) AS work_rest_punch_num

FROM
step3
)

,step5 AS
(
SELECT
*
,DENSE_RANK() OVER (PARTITION BY emp, work_rest_ind ORDER BY work_rest_punch_num) AS work_rest_period_index
FROM
step4
)

,step6 AS
(
SELECT
*
,MIN(logdatetime) OVER (PARTITION BY emp, work_rest_ind, work_rest_period_index) AS work_rest_period_begin
,MAX(next_logdatetime) OVER (PARTITION BY emp, work_rest_ind, work_rest_period_index) AS work_rest_period_end
FROM
step5

)

,step7 AS
(
SELECT
*
,ROUND(CAST(DATEDIFF(MINUTE, work_rest_period_begin, work_rest_period_end) AS FLOAT)/60,2) AS period_length_hrs
,ROW_NUMBER() OVER (PARTITION BY emp, work_rest_ind, work_rest_period_begin, work_rest_period_end ORDER BY (SELECT NULL)) AS duplicate_row_num
FROM
step6
)

SELECT
emp
,work_rest_ind
,CAST(FORMAT(work_rest_period_begin, 'yyyy-MM-dd hh:mm') AS NCHAR(16)) AS period_begin
,CAST(FORMAT(work_rest_period_end, 'yyyy-MM-dd hh:mm') AS NCHAR(16)) AS period_end
,period_length_hrs

FROM
step7

WHERE
duplicate_row_num = 1

ORDER BY
emp
,work_rest_period_begin```

From your data, that produces the following results:

```emp         work_rest_ind period_begin     period_end       period_length_hrs
----------- ------------- ---------------- ---------------- ----------------------
3           WORK          2017-11-01 02:00 2017-11-02 06:10 16.17
3           REST          2017-11-02 06:10 2017-11-02 10:01 15.85
3           WORK          2017-11-02 10:01 2017-11-03 06:10 8.15
3           REST          2017-11-03 06:10 2017-11-05 06:00 47.83
3           WORK          2017-11-05 06:00 2017-11-05 02:01 8.02
3           REST          2017-11-05 02:01 2017-11-06 02:01 24
3           WORK          2017-11-06 02:01 2017-11-06 10:01 8
3           NULL          2017-11-06 10:01 NULL             NULL```
v2

Top Experts
Last 24hrsThis month
 OriginalGriff 280 Richard MacCutchan 225 Richard Deeming 115 CPallini 70 k5054 55
 OriginalGriff 3,221 Richard MacCutchan 1,205 CPallini 905 Patrice T 605 Richard Deeming 508

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900