sqloracleoracle10ganalytic-functions
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
- PL/SQL arabic displayed as?
- Tuple matching using SQLAlchemy
- How do I copy data from one column to another in the same table?
- Insert leading spaces, then the value to a varchar(10) column
- BigQuery groupby, add row with count of 0 for groupby variable if no rows found
- Calculating difference of dates In Postgresql
- Count / Sum JSON variant attribute Flatten
- MySQL: How to convert seconds to mm:ss format?
- Get mySQL MONTH() to use leading zeros?
- How to use order by with union all in sql?
- Create date periods based on two tables
- Unable to install SQL Server(setup.exe) Exit code decimal: -2061893606
- SQL Server TRIM character
- No PK or FK when exporting SQL Server database
- SQL Server SELECT LAST N Rows
- Oracle SQL query using regexp_substr to get string between 2 strings
- How to select Users who have not received notifications using SQL?
- Fuzzy phrase similarity in SQL Server
- Union two selects removing duplicates base on some columns (not the full row)
- SQL fill gaps with hold
- BigQuery - Transaction is aborted due to concurrent update against table
- SQL one to one relationship vs. single table
- SQL query to fetch persons with more than one postcode address via join table
- php scrape to write to mysql database error
- php mysql connect and create table with no error but no data
- Create login and logout rows from single row
- Alternatives to sliding window frames with RANGE on snowflake
- Snowflake SQL Range between interval
- Cascading Soft Delete
- ERROR: extra data after last expected column on postgresql