Find the number of unique days each employee worked ?

Problem Statement :  Find the number of unique days each employee worked ?

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: