CODEWITHSUNDEEP
oracle-database
K Sly
K Sly

Reputation: 27

How can i track changes in job subid

enter image description hereI have a table that has emp number, job name, jobid and jobsubid. i am want be able to identify cases where a jobsubid and job name changes but jobid remains the same and the job name changes to a different name that the previous case where in both jobid and jobsubid are the same.

Upvotes: 0

Views: 30

Answers (1)

Popeye
Popeye

Reputation: 35910

You can use analytical function for it. But there must be some column to identify the order of records so i have assume that there must be some jobstartdate column

Select t.*,
Case when prev_jobname <> jobname and prev_jobsubid <> jobsubid
Then 'Changed'
Else 'Not changed' 
end as result
(Select t.*
Lag(t.jobname) 
 over (partition by t.empno, t.jobid
    Order by t.jobstartdate) as prev_jobname
Lag(t.jobsubid) 
 over (partition by t.empno, t.jobid
    Order by t.jobstartdate) as prev_jobsubid
From your_table t) t

Cheers!!

Upvotes: 1

Related Questions