是的;通过使用LEAD()分析函数，您可以计算职位和福利表中的下一个 effdt，这使得在范围之间查询更加容易。
就像是：
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_plan
from 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 <= sysdate
and 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_prompt
and b.status = 'A'
and d.the_date between b.effdt and b.next_effdt
order 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
（显然，你可以排除dates, jobs and benefits来自上述查询的子查询，因为您已经拥有这些表。它们仅出现在查询中以模拟包含该数据的表，而无需实际创建表。）。
ETA：这是一个仅根据传入的年份计算 12 个月的版本，这将日期行减少到 12 行，而不是 365/366 行。
不幸的是，您仍然需要不同的，以考虑当您有多行从同一个月开始的情况。
例如，对于以下示例中的数据，如果删除了非重复值，您最终将在第 6 个月得到 3 行。然而，不同的操作的行数将远远少于以前。
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_plan
from 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 <= sysdate
and b.status = 'A'
and d.the_date between b.effdt and b.end_effdt
order 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
