我有一个包含以下列的表per_all_assignments_m-
每个分配(_A(
Person_id position_id system_person_type start_date END_DT
1 1 EMP 01-JAN-2019 20-JAN-2019
1 1 EMP 21-JAN-2019 31-DEC-4712
2 1 EMP 01-JAN-2019 03-JUL-2019
2 1 EMP 04-JUL-2019 08-SEP-2019
2 2 EMP 09-SEP-2019 31-DEC-2019
2 2 EMP 01-JAN-2020 31-DEC-4712
3 10 EMP 01-JAN-2019 20-JAN-2019
3 10 EMP 21-JAN-2019 08-SEP-2019
3 10 EMP 09-SEP-2019 20-JAN-2020
3 10 EMP 21-JAN-2020 31-DEC-4712
如果position_id列中有任何值更改，我已经创建了下面的查询来获取。此查询将获取更改日期的日期和上一个开始日期。
select person_id, prev_start_dt, effective_start_date current_start_dt,
case pos_new when pos_old then 1 else pos_old end pos_old
from (
select person_id, position_id pos_new, effective_start_date, effective_end_date,
lag(position_id) over (partition by person_id order by effective_start_date) pos_old,
lag(effective_start_date) over (partition by person_id order by effective_start_date) prev_start_dt,
case effective_start_date when 1 + lag(effective_end_date) over (partition by person_id order by effective_start_date)
then 1 end flag
from per_all_assignments_m
where person_id=1
and assignment_type = 'E')
where flag = 1 and (pos_new <> pos_old )
对于上表，此查询将获取员工#2的09-SEP-2019作为current_start_dt，获取04-JUL-2019作为prev_start_dt
Question-
现在，我想添加一个条件，如果position_id值没有变化，那么应该检索最旧的effective_start_date。
示例-对于EE#1，current_start_dt应为2019年1月1日，prev_start_dt应与EE#3相同，current_start _dt和prev_start _dt应为2019年1月1日。
任何建议都会有很大帮助！
所需输出-
Person_id prev_start_dt current_start_dt pos_old
1 01-JAN-2019 01-JAN-2019 1
2 04-JUL-2019 09-SEP-2019 1
3 01-JAN-2019 01-JAN-2019 10
这里有一种方法：
cte2获取有更改的person_id，结果是person_id行，并添加第一次出现的没有更改的person_id
Fiddle
with cte as
(
select 1 as Person_id, 1 as position_id, 'EMP' as system_person_type, STR_TO_DATE('01-JAN-2019', "%d-%M-%Y") as start_date, STR_TO_DATE('20-JAN-2019', "%d-%M-%Y") as END_DT
union select 1, 1 , 'EMP' , STR_TO_DATE('21-JAN-2019', "%d-%M-%Y"), STR_TO_DATE('31-DEC-4712', "%d-%M-%Y")
union select 2 , 1 , 'EMP' , STR_TO_DATE('01-JAN-2019', "%d-%M-%Y") , STR_TO_DATE('03-JUL-2019', "%d-%M-%Y")
union select 2 , 1 , 'EMP' , STR_TO_DATE('04-JUL-2019', "%d-%M-%Y") , STR_TO_DATE('08-SEP-2019', "%d-%M-%Y")
union select 2 , 2 , 'EMP' , STR_TO_DATE('09-SEP-2019', "%d-%M-%Y") , STR_TO_DATE('31-DEC-2019' , "%d-%M-%Y")
union select 2 , 2 , 'EMP' , STR_TO_DATE('01-JAN-2020', "%d-%M-%Y") , STR_TO_DATE('31-DEC-4712', "%d-%M-%Y")
union select 3 , 10 , 'EMP' , STR_TO_DATE('01-JAN-2019', "%d-%M-%Y") , STR_TO_DATE('20-JAN-2019' , "%d-%M-%Y")
union select 3 , 10 , 'EMP' , STR_TO_DATE('21-JAN-2019', "%d-%M-%Y") , STR_TO_DATE('08-SEP-2019', "%d-%M-%Y")
union select 3 , 10 , 'EMP' , STR_TO_DATE('09-SEP-2019', "%d-%M-%Y") , STR_TO_DATE('20-JAN-2020', "%d-%M-%Y")
union select 3 , 10 , 'EMP' , STR_TO_DATE('21-JAN-2020', "%d-%M-%Y") ,STR_TO_DATE('31-DEC-4712', "%d-%M-%Y");
),
cte2 as
(
select a.Person_id, ( (b.start_date)) as prev_start_dt, (a.start_date) as current_start_dt, (b.position_id)
from cte a left join cte b on a.Person_id = b.Person_id and a.start_date > b.start_date and a.position_id <> b.position_id
and not exists(select 1 from cte c where a.Person_id = c.Person_id and a.start_date > c.start_date and c.start_date > b.start_date)
)
select Person_id, prev_start_dt, current_start_dt, position_id from cte2 where position_id is not null
union
select a.Person_id, a.start_date, a.start_date, a.position_id from cte a where not exists(select 1 from cte b where a.Person_id = b.Person_id and a.start_date > b.start_date)
and a.Person_id not in (select Person_id from cte2 where position_id is not null)
Output:
Person_id prev_start_dt current_start_dt position_id
1 2019-01-01 2019-01-01 1
2 2019-07-04 2019-09-09 1
3 2019-01-01 2019-01-01 10
如果您想跟踪仓位ID发生变化的所有情况，下面这样的方法应该有效：
with cte as
(
select 1 as Person_id,1 as position_id,'EMP' as system_person_type,cast('01Jan2019' as date) as Start_date,cast('20Jan2019' as date) as end_dt from dual union
select 1 as Person_id,1 as position_id,'EMP' as system_person_type,cast('21Jan2019' as date) as Start_date,cast('31Dec4712' as date) as end_dt from dual union
select 2 as Person_id,1 as position_id,'EMP' as system_person_type,cast('01Jan2019' as date) as Start_date,cast('03Jul2019' as date) as end_dt from dual union
select 2 as Person_id,1 as position_id,'EMP' as system_person_type,cast('04Jul2019' as date) as Start_date,cast('08Sep2019' as date) as end_dt from dual union
select 2 as Person_id,2 as position_id,'EMP' as system_person_type,cast('09Sep2019' as date) as Start_date,cast('31Dec2019' as date) as end_dt from dual union
select 2 as Person_id,2 as position_id,'EMP' as system_person_type,cast('01Jan2020' as date) as Start_date,cast('31Dec4712' as date) as end_dt from dual union
select 3 as Person_id,10 as position_id,'EMP' as system_person_type,cast('01Jan2019' as date) as Start_date,cast('20Jan2019' as date) as end_dt from dual union
select 3 as Person_id,10 as position_id,'EMP' as system_person_type,cast('21Jan2019' as date) as Start_date,cast('08Sep2019' as date) as end_dt from dual union
select 3 as Person_id,10 as position_id,'EMP' as system_person_type,cast('09Sep2019' as date) as Start_date,cast('20Jan2020' as date) as end_dt from dual union
select 3 as Person_id,10 as position_id,'EMP' as system_person_type,cast('21Jan2020' as date) as Start_date,cast('31Dec4712' as date) as end_dt from dual
)
select x.Person_Id,
COALESCE(x.lag_start,x.min_date) as Prev_start_dt,
x.min_date as Current_Start_Dt,
COALESCE(x.lag_pos,x.position_id) as Pos_old
FROM
(select person_id,
position_id,
min(start_date) as min_date,
max(start_date) as max_date,
lag(max(start_date)) Over(Partition by Person_Id Order by max(start_date)) as lag_start,
lag(position_id) Over(Partition by Person_Id Order by min(start_date)) as lag_pos
from cte
group by person_id, position_id) x
inner join
(select person_id,
count(distinct position_id) as cnt
from cte
group by person_id) y
ON x.person_id=y.person_id
Where x.Position_id<>x.lag_pos or y.cnt=1
这个想法是在个人和职位级别上对信息进行分组，然后使用滞后函数来确定是否有变化。应用Cnt=1滤波器来获得没有位置变化的情况。希望这能有所帮助。