Get specific series values in order
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.
Solution
You can use a MODEL clause:
SELECT emp_id,
current_movement,
flag,
previous_movement,
next_movement,
(next_movement - current_movement) DAY TO SECOND AS diff,
check_status
FROM (
SELECT m.*,
ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY timeinout) AS rn
FROM my_table m
)
MODEL
PARTITION BY (emp_id)
DIMENSION BY (rn)
MEASURES (
timeinout AS current_movement,
flag,
CAST(NULL AS DATE) AS first_movement,
CAST(NULL AS DATE) AS previous_movement,
CAST(NULL AS DATE) AS next_movement,
CAST(NULL AS VARCHAR2(11)) AS check_status
)
RULES (
first_movement[1] = current_movement[1],
first_movement[rn>1] = CASE
WHEN current_movement[cv(rn)] < first_movement[cv(rn)-1] + INTERVAL '12' HOUR
THEN first_movement[cv(rn)-1]
ELSE current_movement[cv(rn)]
END,
previous_movement[1] = NULL,
previous_movement[rn>1] = CASE
WHEN current_movement[cv(rn)] < first_movement[cv(rn)-1] + INTERVAL '12' HOUR
THEN current_movement[cv(rn)-1]
ELSE NULL
END,
next_movement[1] = current_movement[cv(rn)+1],
next_movement[rn>1] = CASE
WHEN current_movement[cv(rn)+1] < first_movement[cv(rn)] + INTERVAL '12' HOUR
THEN current_movement[cv(rn)+1]
ELSE NULL
END,
check_status[1] = 'FIRST_CHECK',
check_status[rn>1] = CASE
WHEN first_movement[cv(rn)-1] != first_movement[cv(rn)]
THEN 'FIRST_CHECK'
WHEN current_movement[cv(rn)+1] < first_movement[cv(rn)] + INTERVAL '12' HOUR
THEN 'DURING_DAY'
ELSE 'LAST_CHECK'
END
)
Which 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
- Query to find position in a string before keyword and extract data before that position
- SQL: get next relative day of week. (Next Monday, Tuesday, Wed.....)
- #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’
- java.sql.SQLException: Column Index out of range, 2 > 1
- Should LIKE 'searchstr%' use an index?
- Multiple STRING_AGG in one query
- Postgres: Add constraint if it doesn't already exist
- UPDATE Statement ignores INNER JOIN
- Execute Stored Procedure Minimal API .Net
- Change default schema for user
- Oracle SQL - Select oldest of 3 dates, but ignore NULLs
- Housing Society management system database structure
- Keep getting error: PLS-00103: Encountered the symbol "CREATE"
- How to use an UPDATE Query with an INNER JOIN to update fields within a table
- Extract 4 million rows from database in chunks of 1 million rows each
- Aggregate into custom groups
- SQL query for chat application
- SQL basic problem when trying to SELECT 4 atributes and use GROUP BY with only 2
- How to modify query converting DATEDIFF to Oracle accepted syntax?
- Sales data analysis: calculate percentage of each product as per total sales
- Calculate the sum of values for the columns that meet the criteria
- Insert missing rows in time series consumption history
- Oracle database dependencies in PL/SQL
- How to autofill a field with data from another field in the same table?
- In Oracle: How to sort by group/cluster by a column value
- oracle SQL query - add date rows using different date
- How to fix products without cover image in PrestaShop
- IF ROW_COUNT() return error
- Select with like is not returning the correct results
- I am trying to get the start date ONLY from the column below using a SUBSTRING. Can someone tell me what I'm doing wrong?