If time difference for 'Login_Date' column with records for same Department is within 18 hours then pick only the record with earliest login date.

Below sample data:

Need query for below data:

CodePudding user response：

Try this:

Select Record_ID,d.Department,User,d.Login_Date
from data d
inner join ( Select Department,min(Login_Date) 
             from data 
             where EXTRACT(HOUR FROM CAST(Login_Date AS timestamp)) <= 18
             group by Department,trunc(Login_Date) ) as t
on d.Department= t.Department and d.Login_Date = t.Login_Date

CodePudding user response：

From Oracle 12, you can use MATCH_RECOGNIZE to do row-by-row processing if you want to exclude all rows that are within 18 hours of the first row of the group:

SELECT *
FROM   table_name
MATCH_RECOGNIZE (
  PARTITION BY department
  ORDER     BY login_date
  ALL ROWS PER MATCH
  PATTERN (first_row {- within_18_hours* -} )
  DEFINE
    within_18_hours AS login_date <= first_row.login_date   INTERVAL '18' HOUR
)

Which, for the sample data:

CREATE TABLE table_name (record_id, department, "USER", login_date) AS
SELECT 1, 'IT',      'xujk', DATE '2022-01-10'   INTERVAL '10' HOUR FROM DUAL UNION ALL
SELECT 2, 'IT',      'jkl',  DATE '2022-01-10'   INTERVAL '15' HOUR FROM DUAL UNION ALL
SELECT 3, 'IT',      'xujk', DATE '2022-01-12'   INTERVAL '11' HOUR FROM DUAL UNION ALL
SELECT 4, 'FINANCE', 'mno',  DATE '2022-01-10'   INTERVAL '01' HOUR FROM DUAL UNION ALL
SELECT 5, 'FINANCE', 'abc',  DATE '2022-01-12'   INTERVAL '15' HOUR FROM DUAL UNION ALL
SELECT 6, 'FINANCE', 'def',  DATE '2022-01-12'   INTERVAL '20' HOUR FROM DUAL;

Outputs:

DEPARTMENT	LOGIN_DATE	RECORD_ID	USER
FINANCE	10-JAN-22	4	mno
FINANCE	12-JAN-22	5	abc
IT	10-JAN-22	1	xujk
IT	12-JAN-22	3	xujk

db<>fiddle here