Q:

How can i track changes in job subid

I 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.

A:

You can use analytical function for it. But there must be some columm 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!!

