I'm trying to make use of the lead and lag functionality that checks for 'N>1'
consecutive absent_dates but seem to be struggling. I was hoping someone can please suggest a solution
Note I know the employees table isn't included in the query yet to obtain first_name and last_name as I am trying to keep the test case as simple as possible for now.
Below is my test CASE and the desired output should be as follows. Thanks in advance to all that answer.
EMPLOYEE_ID ABSENT_DATE
1 14-JUL-21 Jane Doe
1 15-JUL-21 Jane Doe
1 30-JUL-21 Jane Doe
1 31-JUL-21 Jane Doe
4 22-JUL-21 Mike Jones
4 23-JUL-21 Mike Jones
Create table employees(
employee_id NUMBER(6),
first_name VARCHAR2(20),
last_name VARCHAR2(20),
card_num VARCHAR2(10),
work_days VARCHAR2(7)
);
ALTER TABLE employees
ADD ( CONSTRAINT employees\_pk
PRIMARY KEY (employee\_id));
INSERT INTO employees
(
EMPLOYEE\_ID,
first\_name,
last\_name,
card\_num,
work\_days
)
WITH names AS (
SELECT 1, 'Jane', 'Doe','F123456', 'NYYYYYN'FROM dual UNION ALL
SELECT 2, 'Madison', 'Smith','R33432','NYYYYYN'
FROM dual UNION ALL
SELECT 3, 'Justin', 'Case','C765341','NYYYYYN'
FROM dual UNION ALL
SELECT 4, 'Mike', 'Jones','D564311','NYYYYYN' FROM dual )
SELECT * FROM names;
create table absences(
seq\_num integer GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
employee\_id NUMBER(6),
absent\_date DATE,
constraint absence\_chk check (absent\_date=trunc(absent\_date, 'dd')),
constraint absence\_pk primary key (employee\_id, absent\_date)
);
begin
insert into absences values (1,1, date'2021-07-21');
insert into absences values (2,4, date'2021-07-22');
insert into absences values (3,4, date'2021-07-23');
insert into absences values (4,4, date'2021-07-26');
insert into absences values (5,1, date'2021-07-30');
insert into absences values (6,1, date'2021-07-31');
insert into absences values (7,4, date'2021-07-13');
insert into absences values (8,1, date'2021-07-14');
insert into absences values (9,1, date'2021-07-15');
commit;
end;
-- problem with query
select *
(
select *, lead(absent_date) over(partition by employee_id order by absent_date) nxt,
lag(absent_date) over(partition by employee_id order by absent_date) prev
from absences
) t
where absent_date = prev + 1 or absent_date = nxt - 1;