In trying to make use of a recursive common table expressions and lead analytical function that checks for 'N>1' consecutive absent_dates but seem to be struggling.
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.
Below is my test CASE.
The desired output should be as follows:
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;
-- Different solutions to answer my question
WITH multi_day as (
-- Uses tabibitosan method to look for sequential groups
select employee_id
,absent_date
-- tabibitosan... date-row number gives a constant value (in this case date)
-- where the dates are sequential
,absent_date-row_number() over (partition by employee_id order by absent_date) as grp
from absences
order by. employee_id, absent_date
)
select. m.employee_id
,e.first_name
,e.last_name
,min(m.absent_date) as start_of_absence
,max(m.absent_date) as end_of_absensce
,count(*) as days_absent
from multi_day m
join employees e on (e.employee_id = m.employee_id)
group by m.employee_id
,m.grp
,e.first_name
,e.last_name
having count(*) > 1
order by 1,2;
WITH tab as (
-- Uses tabibitosan method to look for sequential groups
select employee_id
,absent_date
-- tabibitosan... date-row number gives a constant value (in this case date)
-- where the dates are sequential
,absent_date-row_number() over (partition by employee_id order by absent_date) as grp
from absences
)
,multi_day as (
select employee_id
,absent_date
,count(*) over (partition by employee_id, grp) as grp_cnt
from tab
)
select m.employee_id
,e.first_name
,e.last_name
,m.absent_date
from multi_day m
join employees e on (e.employee_id = m.employee_id)
where grp_cnt > 1
order by 1,2;
WITH consecutive_absences AS
(
SELECT a.absent_date,
a.employee_id,
e.first_name,
e.last_name,
LEAD (a.absent_date) OVER ( PARTITION BY a.employee_id
ORDER BY a.absent_date
) AS next_date
, LAG (a.absent_date) OVER ( PARTITION BY a.employee_id
ORDER BY a.absent_date
) AS prev_date
FROM absences a
join employees e on (e.employee_id = a.employee_id)
)
SELECT employee_id,
first_name,
last_name,
absent_date
FROM consecutive_absences
ORDER BY employee_id, absent_date;
SELECT a.employee_id, a.absent_date
, e.first_name, e.last_name
FROM absences
MATCH_RECOGNIZE
(
PARTITION BY employee_id
ORDER BY absent_date
ALL ROWS PER MATCH
PATTERN (frst nxt +)
DEFINE nxt AS absent_date <= PREV (absent_date) + 1
) a
JOIN employees e ON e.employee_id = a.employee_id
ORDER BY employee_id, absent_date
;
