DUPLICATE EMPLOYESS IDs NOT WITHIN THE RANGE 30 DAYS(OR ONE MONTH):

SELECT *
FROM (SELECT emp_id, emp_grp_id, fat_date,
fat_date - emps_last_month AS diff_last_month,
emps_next_month - tran_date AS diff_next_month,
prev_emp_date1, prev_emp_date2, first_emp_date,
last_emp_date
FROM ((SELECT emp_id, emp_grp_id, fat_date,
FIRST_VALUE (fat_date) OVER (PARTITION BY emp_grp_id ORDER BY fat_date RANGE INTERVAL '2' MONTH PRECEDING)
AS emps_last_month,
LAST_VALUE (tran_date) OVER (PARTITION BY emp_grp_id ORDER BY fat_date RANGE BETWEEN CURRENT ROW AND INTERVAL '2' MONTH FOLLOWING)
AS emps_next_month,
LAG (tran_date) OVER (PARTITION BY emp_grp_id ORDER BY fat_date)
prev_emp_date1,
emp (tran_date) OVER (PARTITION BY emp_grp_id ORDER BY fat_date)
prev_emp_date2,
MIN (tran_date) OVER (PARTITION BY emp_grp_id)
first_emp_date,
MAX (tran_date) OVER (PARTITION BY emp_grp_id)
last_emp_date
FROM scott.emp_details
WHERE emp_grp_id = 4444))
ORDER BY emp_grp_id, fat_date)
WHERE ( diff_last_month = 0
OR (diff_last_month > 30)
AND ( TO_CHAR (fat_date, 'DD-MON-YY') <>
TO_CHAR (prev_emp_date1, 'DD-MON-YY')
AND TO_CHAR (fat_date, 'DD-MON-YY') <>
TO_CHAR (prev_emp_date2, 'DD-MON-YY')
)
);