Problem Statement : Find the number of unique days each employee worked ?
Table Name : unq_days
Expected Output :
Table Name : unq_days
Expected Output :
Script to Populate the Above sample table Data :
create table unq_days(
emp_id number,
task_id number,
start_dy varchar2(20),
end_dy varchar2(20)
);
insert into unq_days values(2,1,'Tuesday','Wednesday');
insert into unq_days values(2,1,'Monday','Friday');
insert into unq_days values(1,3,'Friday','Friday');
insert into unq_days values(1,2,'Monday','Tuesday');
insert into unq_days values(1,1,'Monday','Wednesday');
Solution:
Step 1:- Create a table to find the days between and populate the data as per below script
create table days_table (
id number (2) ,
weekdays varchar2(20));
insert into days_table values (1,'Monday');
insert into days_table values (2,'Tuesday');
insert into days_table values (3,'Wednesday');
insert into days_table values (4,'Thursday');
insert into days_table values (5,'Friday');
insert into days_table values (6,'Saturday');
insert into days_table values (7,'Sunday');
Step 2:- Create a function to convert the days into a number value which we can use to find the range of between in step 3 .
CREATE OR REPLACE FUNCTION Fn_days_working(i_days varchar2)
RETURN number IS
v_id number :=0 ;
BEGIN
SELECT id into v_id
FROM days_table t where t.weekdays=i_days;
RETURN v_id ;
END;
Step 3:- Final query
SELECT Emp_id , count(1) from (
with tab as (
select t.emp_id , LISTAGG(t.working_days,',') wrdy from
(
select ud.emp_id ,
(select LISTAGG(distinct t.weekdays ,',')from days_table t
where t.id between Fn_days_working(ud.start_dy) and Fn_days_working(ud.end_dy)
) working_days
from unq_days ud
) t
group by t.emp_id
)
SELECT DISTINCT emp_id, trim(regexp_substr(wrdy, '[^,]+', 1, LEVEL)) str
FROM TAB
CONNECT BY LEVEL <= regexp_count(WRDY, ',')+1
)
group by emp_id ;
I am interested in hearing your feedback about this article , So that I can improve my lab series on sql for you.connect with us on facebook
Share
Did you enjoy reading this and found it useful? If so, please share it with your friends:
