I have a table contains the info of the access time and movements for each employee.
Some employees have shift work hours between two days, I have column determine if this access time belongs to the previous or current day.
I need to collect the access time which belongs to one day, then get the difference between the current access time and the next one and determine the check status of the movement(first or last or during the day movement).
I get the mentioned result if the employee has shift work hours on the same day but I can't get the true result if he has work hours between two days.
Example:
Employee Number 1 has Shift method: 17:00 -> 01:00
Employee Number 2 has Shift method: 08:30 -> 16:30
Total Work Hours: 08:00
CREATE TABLE My_Table (
EMP_ID NUMBER(4) ,
Timeinout date ,
flag number(1)
);
INSERT INTO My_Table VALUES (1,to_date('03-07-2018 16:39:44','dd-mm-yyyy hh24:mi:ss'),0);
INSERT INTO My_Table VALUES (1,to_date('04-07-2018 01:14:40','dd-mm-yyyy hh24:mi:ss'),1);
INSERT INTO My_Table VALUES (1,to_date('04-07-2018 01:14:44 ','dd-mm-yyyy hh24:mi:ss'),1);
INSERT INTO My_Table VALUES (1,to_date('04-07-2018 16:14:52','dd-mm-yyyy hh24:mi:ss'),0);
INSERT INTO My_Table VALUES (1,to_date('05-07-2018 01:07:40','dd-mm-yyyy hh24:mi:ss'),1);
INSERT INTO My_Table VALUES (1,to_date('05-07-2018 01:07:44 ','dd-mm-yyyy hh24:mi:ss'),1);
INSERT INTO My_Table VALUES (1,to_date('05-07-2018 16:31:08','dd-mm-yyyy hh24:mi:ss'),0);
INSERT INTO My_Table VALUES (1,to_date('06-07-2018 01:01:48 ','dd-mm-yyyy hh24:mi:ss'),1);
INSERT INTO My_Table VALUES (1,to_date('06-07-2018 01:01:52','dd-mm-yyyy hh24:mi:ss'),1);
INSERT INTO My_Table VALUES (2,to_date('03-07-2018 08:37:40','dd-mm-yyyy hh24:mi:ss'),0);
INSERT INTO My_Table VALUES (2,to_date('03-07-2018 16:27:36','dd-mm-yyyy hh24:mi:ss'),0);
INSERT INTO My_Table VALUES (2,to_date('04-07-2018 08:37:04 ','dd-mm-yyyy hh24:mi:ss'),0);
INSERT INTO My_Table VALUES (2,to_date('04-07-2018 12:58:36','dd-mm-yyyy hh24:mi:ss'),0);
INSERT INTO My_Table VALUES (2,to_date('04-07-2018 13:09:48 ','dd-mm-yyyy hh24:mi:ss'),0);
INSERT INTO My_Table VALUES (2,to_date('04-07-2018 17:15:32 ','dd-mm-yyyy hh24:mi:ss'),0);
COMMIT;
The query I used is for employee number2 return the true result:
select X.*,
CASE
WHEN Previous_Movment IS NULL AND Next_Movment IS NOT NULL THEN
'First Check'
WHEN Previous_Movment IS NOT NULL AND Next_Movment IS NULL THEN
'Last Check'
when Previous_Movment IS NULL AND Next_Movment IS NULL THEN
'Do Not have access in this day'
ELSE
'During the Day'
end CHECK_sTATUS
from (select trunc(a.timeinout) as date_,
a.timeinout current_movment,
LEAD(TIMEINOUT) OVER(partition by trunc(TIMEINOUT), Emp_ID ORDER BY TIMEINOUT, Emp_ID) Next_Movment,
LAG(TIMEINOUT) OVER(partition by trunc(TIMEINOUT), Emp_ID ORDER BY TIMEINOUT, Emp_ID) Previous_Movment,
trunc(24 *
mod(LEAD(TIMEINOUT) OVER(partition by trunc(TIMEINOUT),
Emp_ID ORDER BY TIMEINOUT,
Emp_ID) - TIMEINOUT,
1)) as diff_hours,
trunc(mod(mod(LEAD(TIMEINOUT)
OVER(partition by trunc(TIMEINOUT),
Emp_ID ORDER BY TIMEINOUT,
Emp_ID) - TIMEINOUT,
1) * 24,
1) * 60) as diff_minus,
trunc(mod(mod(mod(LEAD(TIMEINOUT)
OVER(partition by trunc(TIMEINOUT),
Emp_ID ORDER BY TIMEINOUT,
Emp_ID) - TIMEINOUT,
1) * 24,
1) * 60,
1) * 60) as diff_sec,
Emp_ID,
FLAG Return_Previous_day_or_not
from My_table a
WHERE trunc(a.timeinout) between
to_Date('03-07-2018', 'dd-mm-yyyy') and
to_Date('07-07-2018', 'dd-mm-yyyy')
and a.Emp_ID = 2
) X
and the get the below result for employee number2:
But when I switch the query to employee number1 I get the wrong result because the movements between two days:
Note:
I used Trunc(Timeinout) in the LEAD & lAG function for the employee who has work hours on the same day.
CodePudding user response：
From Oracle 12, you can use MATCH_RECOGNIZE for this.
Assuming that you want a shift to last for no more than 12 hours (yours appear to be 8 hours and people check in early and leave later) then:
SELECT emp_id,
timeinout AS current_movement,
flag,
CASE
WHEN cls IN ('FIRST_CHECK', 'DURING_DAY')
THEN next_timeinout
END AS next_movement,
CASE
WHEN cls IN ('DURING_DAY', 'LAST_CHECK')
THEN prev_timeinout
END AS previous_movement,
CASE
WHEN cls IN ('FIRST_CHECK', 'DURING_DAY')
THEN (next_timeinout - timeinout) DAY TO SECOND
END AS diff,
cls AS check_status
FROM my_table
MATCH_RECOGNIZE(
PARTITION BY emp_id
ORDER BY timeinout
MEASURES
PREV(timeinout) AS prev_timeinout,
NEXT(timeinout) AS next_timeinout,
LAST(last_check.timeinout) AS last_timeinout,
CLASSIFIER() AS cls
ALL ROWS PER MATCH
PATTERN (first_check (during_day* last_check)?)
DEFINE
during_day AS timeinout < first_check.timeinout INTERVAL '12' HOUR,
last_check AS timeinout < first_check.timeinout INTERVAL '12' HOUR
) m
Which, for your sample data, outputs:
EMP_ID CURRENT_MOVEMENT FLAG NEXT_MOVEMENT PREVIOUS_MOVEMENT DIFF CHECK_STATUS 1 2018-07-03T16:39:44 0 2018-07-04T01:14:40 00 08:34:56.000000 FIRST_CHECK 1 2018-07-04T01:14:40 1 2018-07-04T01:14:44 2018-07-03T16:39:44 00 00:00:04.000000 DURING_DAY 1 2018-07-04T01:14:44 1 2018-07-04T01:14:40 LAST_CHECK 1 2018-07-04T16:14:52 0 2018-07-05T01:07:40 00 08:52:48.000000 FIRST_CHECK 1 2018-07-05T01:07:40 1 2018-07-05T01:07:44 2018-07-04T16:14:52 00 00:00:04.000000 DURING_DAY 1 2018-07-05T01:07:44 1 2018-07-05T01:07:40 LAST_CHECK 1 2018-07-05T16:31:08 0 2018-07-06T01:01:48 00 08:30:40.000000 FIRST_CHECK 1 2018-07-06T01:01:48 1 2018-07-06T01:01:52 2018-07-05T16:31:08 00 00:00:04.000000 DURING_DAY 1 2018-07-06T01:01:52 1 2018-07-06T01:01:48 LAST_CHECK 2 2018-07-03T08:37:40 0 2018-07-03T16:27:36 00 07:49:56.000000 FIRST_CHECK 2 2018-07-03T16:27:36 0 2018-07-03T08:37:40 LAST_CHECK 2 2018-07-04T08:37:04 0 2018-07-04T12:58:36 00 04:21:32.000000 FIRST_CHECK 2 2018-07-04T12:58:36 0 2018-07-04T13:09:48 2018-07-04T08:37:04 00 00:11:12.000000 DURING_DAY 2 2018-07-04T13:09:48 0 2018-07-04T17:15:32 2018-07-04T12:58:36 00 04:05:44.000000 DURING_DAY 2 2018-07-04T17:15:32 0 2018-07-04T13:09:48 LAST_CHECK
db<>fiddle here
CodePudding user response：
From Oracle 11gR2, you can use a recursive sub-query:
WITH ordered_data AS (
SELECT m.*,
ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY timeinout) AS rn,
LAG(timeinout) OVER (PARTITION BY emp_id ORDER BY timeinout)
AS previous_movement,
LEAD(timeinout) OVER (PARTITION BY emp_id ORDER BY timeinout)
AS next_movement
FROM my_table m
),
grouped_data (
emp_id,
current_movement,
flag,
rn,
previous_movement,
next_movement,
grp,
check_status,
first_movement
) AS (
SELECT emp_id,
timeinout,
flag,
rn,
NULL,
next_movement,
1,
'FIRST_CHECK',
timeinout
FROM ordered_data
WHERE rn = 1
UNION ALL
SELECT o.emp_id,
o.timeinout AS current_movement,
o.flag,
o.rn,
CASE
WHEN o.timeinout < g.first_movement INTERVAL '12' HOUR
THEN o.previous_movement
ELSE NULL
END,
CASE
WHEN o.timeinout < g.first_movement INTERVAL '12' HOUR
AND ( o.next_movement >= g.first_movement INTERVAL '12' HOUR
OR o.next_movement IS NULL)
THEN NULL
ELSE o.next_movement
END,
CASE
WHEN o.timeinout < g.first_movement INTERVAL '12' HOUR
THEN g.grp
ELSE g.grp 1
END,
CASE
WHEN o.timeinout < g.first_movement INTERVAL '12' HOUR
AND ( o.next_movement >= g.first_movement INTERVAL '12' HOUR
OR o.next_movement IS NULL)
THEN 'LAST_CHECK'
WHEN o.timeinout < g.first_movement INTERVAL '12' HOUR
THEN 'DURING_DAY'
ELSE 'FIRST_CHECK'
END,
CASE
WHEN o.timeinout < g.first_movement INTERVAL '12' HOUR
THEN g.first_movement
ELSE o.timeinout
END
FROM ordered_data o
INNER JOIN grouped_data g
ON (o.emp_id = g.emp_id AND o.rn = g.rn 1)
)
SELECT emp_id,
current_movement,
flag,
previous_movement,
next_movement,
(next_movement - current_movement) DAY TO SECOND AS diff,
check_status
FROM grouped_data
ORDER BY emp_id, current_movement
db<>fiddle here
CodePudding user response：
You can use a PIPELINED function:
CREATE TYPE my_table_type AS OBJECT (
emp_id NUMBER,
current_movement DATE,
flag NUMBER,
previous_movement DATE,
next_movement DATE,
diff INTERVAL DAY TO SECOND,
check_status VARCHAR2(15)
);
CREATE TYPE my_table_tbltype AS TABLE OF my_table_type;
Then:
CREATE FUNCTION parse_my_table RETURN my_table_tbltype PIPELINED
AS
c_shift_length CONSTANT INTERVAL DAY TO SECOND := INTERVAL '12' HOUR;
v_emp_id MY_TABLE.EMP_ID%TYPE;
v_first_movement MY_TABLE.TIMEINOUT%TYPE;
v_previous_movement MY_TABLE.TIMEINOUT%TYPE;
v_next_movement MY_TABLE.TIMEINOUT%TYPE;
v_check_status VARCHAR2(15);
BEGIN
FOR row IN (
SELECT m.*,
ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY timeinout) AS rn,
LAG(timeinout) OVER (PARTITION BY emp_id ORDER BY timeinout)
AS previous_movement,
LEAD(timeinout) OVER (PARTITION BY emp_id ORDER BY timeinout)
AS next_movement
FROM my_table m
ORDER BY m.emp_id, m.timeinout
)
LOOP
IF v_emp_id IS NULL
OR v_emp_id != row.emp_id
OR row.timeinout >= v_first_movement c_shift_length THEN
v_emp_id := row.emp_id;
v_first_movement := row.timeinout;
v_previous_movement := NULL;
v_next_movement := row.next_movement;
v_check_status := 'FIRST_CHECK';
ELSIF row.next_movement >= v_first_movement c_shift_length
OR row.next_movement IS NULL THEN
v_previous_movement := row.previous_movement;
v_next_movement := NULL;
v_check_status := 'LAST_CHECK';
ELSE
v_previous_movement := row.previous_movement;
v_next_movement := row.next_movement;
v_check_status := 'DURING_DAY';
END IF;
PIPE ROW (
my_table_type(
row.emp_id,
row.timeinout,
row.flag,
v_previous_movement,
v_next_movement,
(v_next_movement - row.timeinout) DAY TO SECOND,
v_check_status
)
);
END LOOP;
END;
/
Then:
SELECT *
FROM TABLE(parse_my_table());
Outputs:
EMP_ID CURRENT_MOVEMENT FLAG PREVIOUS_MOVEMENT NEXT_MOVEMENT DIFF CHECK_STATUS 1 2018-07-03T16:39:44 0 2018-07-04T01:14:40 00 08:34:56.000000 FIRST_CHECK 1 2018-07-04T01:14:40 1 2018-07-03T16:39:44 2018-07-04T01:14:44 00 00:00:04.000000 DURING_DAY 1 2018-07-04T01:14:44 1 2018-07-04T01:14:40 LAST_CHECK 1 2018-07-04T16:14:52 0 2018-07-05T01:07:40 00 08:52:48.000000 FIRST_CHECK 1 2018-07-05T01:07:40 1 2018-07-04T16:14:52 2018-07-05T01:07:44 00 00:00:04.000000 DURING_DAY 1 2018-07-05T01:07:44 1 2018-07-05T01:07:40 LAST_CHECK 1 2018-07-05T16:31:08 0 2018-07-06T01:01:48 00 08:30:40.000000 FIRST_CHECK 1 2018-07-06T01:01:48 1 2018-07-05T16:31:08 2018-07-06T01:01:52 00 00:00:04.000000 DURING_DAY 1 2018-07-06T01:01:52 1 2018-07-06T01:01:48 LAST_CHECK 2 2018-07-03T08:37:40 0 2018-07-03T16:27:36 00 07:49:56.000000 FIRST_CHECK 2 2018-07-03T16:27:36 0 2018-07-03T08:37:40 LAST_CHECK 2 2018-07-04T08:37:04 0 2018-07-04T12:58:36 00 04:21:32.000000 FIRST_CHECK 2 2018-07-04T12:58:36 0 2018-07-04T08:37:04 2018-07-04T13:09:48 00 00:11:12.000000 DURING_DAY 2 2018-07-04T13:09:48 0 2018-07-04T12:58:36 2018-07-04T17:15:32 00 04:05:44.000000 DURING_DAY 2 2018-07-04T17:15:32 0 2018-07-04T13:09:48 LAST_CHECK
db<>fiddle here