CODEWITHSUNDEEP
sqloracle-database
Vijay
Vijay

Reputation: 15

Employee table and need output in the below format

I have the following table

enter image description here

I need output in the format below: 

EMP_NUM,Current_Department_number,Previous_Department_number

The start and end date(columns) in the table represents employee has worked in Particular department for specific period of time.

Example : From table Employee 102 has started working in department 10 from Jan 2001 to Jan 2002 and then he moved to department 20 where he worked from Jan 2002 to Jan 2003.

But I need the output like this:

EMP_NUM,Current_Department_number,Previous_Department_number

Please let me know if you need any other details!

Upvotes: 0

Views: 85

Answers (3)

Kaval Patel
Kaval Patel

Reputation: 668

Try this solution it will give current department and previous department: 

 ;WITH EmployeeCTE AS (
    SELECT EMPID,
    DEPTID = 
        STUFF((SELECT TOP(2)',' + CONVERT(varchar(10), DEPTID )
               FROM EMPDEPT b 
               WHERE b.EMPID = a.EMPID 
               ORDER BY JDATE DESC
              FOR XML PATH('')), 1, 1, '')
    FROM EMPDEPT a
    GROUP BY EMPID
    )
    SELECT
    EMPID,
    (SELECT Substring(DEPTID, 1, 
    CASE WHEN  CHARINDEX(',', DEPTID ) = 0 then LEN(DEPTID) 
    ELSE CHARINDEX(',', DEPTID) -1 END
    )) as CurrentDept ,
    (SELECT Substring(DEPTID, Charindex(',', DEPTID) + 1, LEN(DEPTID))) as PreviousDept
    FROM EmployeeCTE

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Other method is to use of subquery 

select EMP_NUM, dept_id as Current_Department_number, 
      (select top 1 dept_id from table 
       where EMP_NUM = t.EMP_NUM and start_date < t.start_date order by start_date) as Previous_Department_number
from table t;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think you want lag():

select emp_num, dept_id as Current_Department_number,
       lag(dept_id) over (partition by emp_num order by start_date) as previous_department_number
from t;

Upvotes: 2

Related Questions