Looking for someone who would be willing to share their SQL code for reporting on job history from the PAEMPPOS table. The end result that we'd like is as follows:
There may well be better ways to do this, but I am restricted to SQL SERVER 2008 R2 techniques. (No LEAD/LAG) so if you're 2012+ on SQL Server or on ORACLE you can change the t1/t2 queries to use LEAD/LAG all else should still work.
My "Data" table With clause is only for demonstration purposes. Remove the "data" table and start the WITH at t1.
Change the "FROM data" in t1 to PAYEMPPOS.
SQL:
with data as (select 1 as COMPANY, 'ABC' as PROCESS_LEVEL, 1000 as DEPARTMENT, 3000 as JOB_CODE, convert(DATE,'2016-12-19') as EFFECT_DATE, convert(DATE,'9999-12-31') as END_DATE
union all
select 1 as COMPANY, 'ABC' as PROCESS_LEVEL, 1000 as DEPARTMENT, 3000 as JOB_CODE, convert(DATE,'2016-09-25') as EFFECT_DATE, convert(DATE,'2016-12-18') as END_DATE
select 1 as COMPANY, 'XYZ' as PROCESS_LEVEL, 1000 as DEPARTMENT, 4000 as JOB_CODE, convert(DATE,'2015-06-24') as EFFECT_DATE, convert(DATE,'2016-09-24') as END_DATE
select 1 as COMPANY, 'XYZ' as PROCESS_LEVEL, 1000 as DEPARTMENT, 4000 as JOB_CODE, convert(DATE,'2014-12-21') as EFFECT_DATE, convert(DATE,'2015-06-23') as END_DATE
/*select 1 as COMPANY, 'ABC' as PROCESS_LEVEL, 1000 as DEPARTMENT, 3000 as JOB_CODE, convert(DATE,'2014-11-21') as EFFECT_DATE, convert(DATE,'2014-12-20') as END_DATE
*/select 1 as COMPANY, 'ABC' as PROCESS_LEVEL, 1000 as DEPARTMENT, 3000 as JOB_CODE, convert(DATE,'2013-05-07') as EFFECT_DATE, convert(DATE,'2014-12-20') as END_DATE
select 1 as COMPANY, 'ABC' as PROCESS_LEVEL, 1000 as DEPARTMENT, 5000 as JOB_CODE, convert(DATE,'2011-09-04') as EFFECT_DATE, convert(DATE,'2013-05-06') as END_DATE
select 1 as COMPANY, 'ABC' as PROCESS_LEVEL, 1000 as DEPARTMENT, 5000 as JOB_CODE, convert(DATE,'2011-05-02') as EFFECT_DATE, convert(DATE,'2013-09-03') as END_DATE
select 1 as COMPANY, 'ABC' as PROCESS_LEVEL, 1000 as DEPARTMENT, 5000 as JOB_CODE, convert(DATE,'2011-01-01') as EFFECT_DATE, convert(DATE,'2013-05-01') as END_DATE
),
t1 as (SELECT *,
ROW_NUMBER() over (order by EFFECT_DATE desc) as M,
convert(varchar,COMPANY)+PROCESS_LEVEL+convert(Varchar,DEPARTMENT)+ convert(varchar,JOB_CODE) as KeyVal
from data /*PAEMPPOS where EMPLOYEE = your EMPLOYEE Number*/ ),
t2 as (select *,
CASE WHEN M%2=1 THEN MAX(CASE WHEN M%2=0 THEN KeyVal END) OVER (Partition BY (M+1)/2) ELSE MAX(CASE WHEN M%2=1 THEN KeyVal END) OVER (Partition BY M/2) END LeadKeyVal,
CASE WHEN M%2=1 THEN MAX(CASE WHEN M%2=0 THEN KeyVal END) OVER (Partition BY M/2) ELSE MAX(CASE WHEN M%2=1 THEN KeyVal END) OVER (Partition BY (M+1)/2) END LagKeyVal
from t1),
t3 as (select *,
CASE WHEN KeyVal = LeadKeyVal then null else EFFECT_DATE end as New_EFFECT_DATE
from t2),
t3a as (select *,
rank() over (order by New_EFFECT_DATE desc) as rnk_neweffdate
from t3),
t4 as (select *,
case when KeyVal = LagKeyVal then null else END_DATE end as New_END_DATE
t4a as (select *,
rank() over (order by New_END_DATE desc) as rnk_newenddate
from t4 )
select a.COMPANY,
a.PROCESS_LEVEL,
a.DEPARTMENT,
a.JOB_CODE,
Convert(varchar(10),a.EFFECT_DATE,101) as EFFECT_DATE,
case when c.END_DATE = '9999-12-31' then null else convert(varchar(10),c.END_DATE,101) end as END_DATE
from t3a a join t4a c on
(a.KeyVal = c.KeyVal
and rnk_neweffdate = rnk_newenddate
)
Where a.New_EFFECT_DATE is not null
order by a.EFFECT_DATE desc
