SQL join two record into one row with multiple column

Question

i want to join two record (from same table) into one row with multiple column.

employment history structure as follows:

StaffID     StartDate       EndDate         DeptID
==================================================
1           2010-10-01      2011-01-19      1
1           2011-01-20      2012-12-31      2
1           2013-01-01      2013-05-29      4

how can i join the two rows into one row if same StaffID and the 2nd record startdate is 1 day after the enddate of 1st record (continuous employment)

the output should like this

StaffID     EffectiveDate   New_DeptID      Prev_DeptID     
==================================================
1           2011-01-20      2               1
1           2013-01-01      4               2

the following is my sql statement but it doesn't work

  select distinct
    ca1.StaffID,
    ca1.ProjectDepartment as Prev_DeptID, ca1.StartDate, ca1.EndDate, 
    ca2.ProjectDepartment as New_DeptID, ca2.StartDate, ca2.EndDate
from 
emp_hist as ca1,
emp_hist as ca2
where 
    (ca1.StaffID = ca2.StaffID) 
    and ca1.StartDate<>ca2.StartDate
    and ca1.EndDate <>ca2.EndDate
    and  ca2.startdate= DATEADD(day, 1, ca1.enddate)

for example, two records (true data) in the table:

StaffID     StartDate                   EndDate                     DeptID
===========================================================================
1           2010-04-12 12:00:00.000     2013-02-28 00:00:00.000     1
1           2013-03-01 12:00:00.000     2013-08-29 11:02:59.877     2

i cannot retrieve this record by using my sql statement

hi, i had add some supplementary information. i cannot retrieve the record (true data) by using my sql statement — Joe Yan
– Joe Yan, Commented Aug 29, 2013 at 3:07

User1000547 · Accepted Answer · 2016-11-29 17:22:45Z

1

Your problem is that the dates have a time component. You appear to be using SQL Server. You can fix your query by doing this:

select ca1.StaffID,
       ca1.ProjectDepartment as Prev_DeptID, ca1.StartDate, ca1.EndDate, 
       ca2.ProjectDepartment as New_DeptID, ca2.StartDate, ca2.EndDate
from emp_hist as ca1 join
     emp_hist as ca2
     on ca1.StaffID = ca2.StaffID and
        cast(ca1.StartDate as date) <> cast(ca2.StartDate as date) and
        cast(ca1.EndDate as date) <> cast(ca2.EndDate as date) and
        cast(ca2.startdate as date) = DATEADD(day, 1, cast(ca1.enddate as date));

I also replaced the implicit join with improved join syntax.

edited Nov 29, 2016 at 17:22

User1000547

4,3219 gold badges44 silver badges68 bronze badges

answered Aug 29, 2013 at 3:10

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ejlepoud · Accepted Answer · 2013-08-29 03:59:25Z

1

If you're using SQL 2012 try the lag functions.

select distinct
  ca1.StaffID,
  ca1.EndDate,
  ca1.ProjectDepartment as New_DeptID, 
  LAG(ca1.ProjectDepartment) OVER (PARTITION BY ca1.StaffId ORDER BY ca1.EndDate) as Prev_DeptID
from 
  emp_hist as ca1

If you're not, use the RANK function and a subquery

select 
  eh.StaffID,
  eh.EndDate,
  eh.ProjectDepartment as New_DeptID, 
  eh1.ProjectDepartment as Prev_DeptID
from 
(select *, RANK(EndDate) OVER (PARTITION BY StaffId ORDER BY EndDate) as Rank 
from emp_hist) eh left join (
  select distinct
    StaffID,
    EndDate,
    ProjectDepartment, 
    RANK(EndDate) OVER (PARTITION BY StaffId ORDER BY EndDate) as Rank
from 
  emp_hist) eh1 on eh1.staffid=a.staffid and eh1.rank=eh.rank-1

edited Aug 29, 2013 at 3:59

answered Aug 29, 2013 at 3:53

ejlepoud

1,6082 gold badges12 silver badges5 bronze badges

Collectives™ on Stack Overflow

SQL join two record into one row with multiple column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related