Oracle SQL query to select only records with date earliest on Department Table

Question

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:

Answer 1

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

---

If you want to exclude rows that are within 18 hours of the previous row (and not necessarily within 18 hours of the earliest row of the group) then you can use:

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 <= PREV(login_date) + INTERVAL '18' HOUR
)

db<>fiddle here

Answer 2

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