Help in PL-SQL

[balachandar]

Hi All,
I have a table with the following details
EMPID,EFFDT,EFFSEQ,JOBCODE. Now from this table, I need to
find the current Jobcode and the previous jobcode for every employee using EFFDT and EFFSEQ. For example for the
following rows

1000, Employee1,01-Jan-2005,1,'JobCode1'
1000, Employee1,01-Feb-2005,1,'JobCode2'
2000, Employee1,01-Jan-2005,1,'JobCode3'
2000, Employee1,01-Feb-2005,1,'JobCode4'

My Sql should return the values as

1000, Employee1,01-Feb-2005,1,'JobCode2','JobCode1'
1000, Employee1,01-Jan-2005,1,'JobCode1',''
2000, Employee1,01-Feb-2005,1,'JobCode3','JobCode4'
2000, Employee1,01-Jan-2005,1,'JobCode4' ''

Please note that only SQL solutions are valid. This is going to be exported into a text file through an external interface that can only execute SQL Statements.

Thanks and Regards
Balachandar Ganesan.

 

[taupirho]

Try the following SQL:-

select
effseq,
empid,
effdt,
jobcode,
lag(jobcode) over (partition by effseq order by effseq)
from testtab
order by effseq,effdt desc