Click here to Skip to main content
15,307,242 members
Please Sign up or sign in to vote.
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
Comments
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.

1 solution

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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



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