create table billings(
emp varchar(29),
bill_date date,
bill_rate int
);
insert into billings values
('Sehwag', '01-JAN-1989', 15) ,
('Dhoni', '01-JAN-1989', 20) ,
('Sachin', '05-Feb-1991', 30),
('Sachin','01-JAN-1990',25);
create table HoursWorked
(emp_name varchar(20),
work_date date,
bill_hrs int) ;
insert into HoursWorked values
('Sachin', '01-JUL-1990',3) ,
('Sachin', '01-AUG-1990', 5),
('Sehwag','01-JUL-1990', 2),
('Sachin','01-JUL-1991', 4);
with cte as (select *, row_number () over(partition by emp order by bill_date asc) as rn
,count(*) over (partition by emp) cn
from billings
) ,cte2 as(select a.*,coalesce (b.bill_date,dateadd(day,1,a.bill_date)) as bd from cte a
left join
cte b
on a.emp=b.emp and a.cn=b.cn
and a.bill_date<b.bill_date)
select a.*, b.* from
cte2 a
right join hoursworked b
on
a.emp =b.emp_name
and b.work_date between a.bill_date and a.bd
Telegram: DB developers channel