More efficient way to find employees with coverage between two dates
Yes; by using the LEAD() analytic function, you can calculate the next effdt in the jobs and benefits tables, which makes it easier to query between the ranges.
Something like:
with dates as (select trunc(sysdate, 'yyyy') - 1 + level the_date, to_number(to_char(trunc(sysdate, 'yyyy') - 1 + level, 'mm')) monthofyear, to_number(to_char(sysdate, 'yyyy')) calendar_year from dual connect by level <= 365), jobs as (select 123 emplid, to_date('01/02/2015', 'dd/mm/yyyy') effdt, 900 deptid, 'A' hr_status from dual union all select 123 emplid, to_date('30/06/2015', 'dd/mm/yyyy') effdt, 900 deptid, 'I' hr_status from dual union all select 123 emplid, to_date('01/08/2015', 'dd/mm/yyyy') effdt, 901 deptid, 'A' hr_status from dual), benefits as (select 123 emplid, to_date('01/03/2015', 'dd/mm/yyyy') effdt, 'PPO' benefit_plan, 'A' status from dual union all select 123 emplid, to_date('31/07/2015', 'dd/mm/yyyy') effdt, null benefit_plan, 'I' status from dual union all select 123 emplid, to_date('01/09/2015', 'dd/mm/yyyy') effdt, 'HMO' benefit_plan, 'A' status from dual),-- ********* end of mimicking your tables ********* -- j as (select emplid, effdt, deptid, hr_status, lead(effdt, 1, sysdate) over (partition by emplid order by effdt) next_effdt from jobs), b as (select emplid, effdt, benefit_plan, status, lead(effdt, 1, sysdate) over (partition by emplid order by effdt) next_effdt from benefits)select distinct j.emplid, d.calendar_year, d.monthofyear, j.deptid, b.benefit_planfrom j inner join dates d on (d.the_date >= j.effdt and d.the_date < j.next_effdt) inner join b on (j.emplid = b.emplid)where d.the_date <= sysdateand d.the_date between to_date (:year_prompt || '01-01', 'YYYY-MM-DD') and to_date (:year_prompt || '12-31', 'YYYY-MM-DD') -- if no index on d.the_date, maybe use trunc(the_date, 'yyyy') = :year_promptand b.status = 'A'and d.the_date between b.effdt and b.next_effdtorder by 1, 4, 2, 3; EMPLID CALENDAR_YEAR MONTHOFYEAR DEPTID BENEFIT_PLAN---------- ------------- ----------- ---------- ------------ 123 2015 3 900 PPO 123 2015 4 900 PPO 123 2015 5 900 PPO 123 2015 6 900 PPO 123 2015 7 900 PPO 123 2015 9 901 HMO 123 2015 10 901 HMO 123 2015 11 901 HMO (Obviously, you can exclude the dates, jobs and benefits subqueries from the above query, since you already have those tables. They're only present in the query to simulate having tables with that data in it without needing to actually create the tables.).
ETA: Here's a version that just calculates the 12 months based on the year that's passed in, which reduces the date rows to 12, rather than 365/366 rows.
Unfortunately, you'll still need the distinct, to take account of when you have multiple rows starting in the same month.
For example, with the data in the following example, you would end up with 3 rows for month 6 if you removed the distinct. However, the number of rows the distinct is operating over will be far less than previously.
with dates as (select add_months(to_date(:year_prompt || '-01-01', 'YYYY-MM-DD'), - 1 + level) the_date, level monthofyear, :year_prompt calendar_year -- assuming this is a number from dual connect by level <= 12), jobs as (select 123 emplid, to_date('01/02/2015', 'dd/mm/yyyy') effdt, 900 deptid, 'A' hr_status from dual union all select 123 emplid, to_date('15/06/2015', 'dd/mm/yyyy') effdt, 900 deptid, 'I' hr_status from dual union all select 123 emplid, to_date('26/06/2015', 'dd/mm/yyyy') effdt, 900 deptid, 'A' hr_status from dual union all select 123 emplid, to_date('01/08/2015', 'dd/mm/yyyy') effdt, 901 deptid, 'A' hr_status from dual), benefits as (select 123 emplid, to_date('01/03/2015', 'dd/mm/yyyy') effdt, 'PPO' benefit_plan, 'A' status from dual union all select 123 emplid, to_date('31/07/2015', 'dd/mm/yyyy') effdt, null benefit_plan, 'I' status from dual union all select 123 emplid, to_date('01/09/2015', 'dd/mm/yyyy') effdt, 'HMO' benefit_plan, 'A' status from dual),-- ********* end of mimicking your tables ********* -- j as (select emplid, trunc(effdt, 'mm') effdt, deptid, hr_status, trunc(coalesce(lead(effdt) over (partition by emplid order by effdt) -1, sysdate), 'mm') end_effdt -- subtracting 1 from the lead(effdt) since here since the original sql had d.the_date < j.next_effdt and we need -- to take into account when the next_effdt is the first of the month; we want the previous month to be displayed from jobs), b as (select emplid, trunc(effdt, 'mm') effdt, benefit_plan, status, trunc(lead(effdt, 1, sysdate) over (partition by emplid order by effdt), 'mm') end_effdt from benefits)select distinct j.emplid, d.calendar_year, d.monthofyear, j.deptid, b.benefit_planfrom j inner join dates d on (d.the_date between j.effdt and j.end_effdt) inner join b on (j.emplid = b.emplid)where d.the_date <= sysdateand b.status = 'A'and d.the_date between b.effdt and b.end_effdtorder by 1, 4, 2, 3; EMPLID CALENDAR_YEAR MONTHOFYEAR DEPTID BENEFIT_PLAN ---------- ------------- ----------- ---------- -------------------------------- 123 2015 3 900 PPO 123 2015 4 900 PPO 123 2015 5 900 PPO 123 2015 6 900 PPO 123 2015 6 900 PPO 123 2015 7 900 PPO 123 2015 9 901 HMO 123 2015 10 901 HMO 123 2015 11 901 HMO