SQL join two record into one row with multiple column -

i want join 2 record (from same table) 1 row multiple column.

employment history structure 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 join 2 rows 1 row if same staffid , 2nd record startdate 1 day after enddate of 1st record (continuous employment)

the output should this

staffid     effectivedate   new_deptid      prev_deptid      ================================================== 1           2011-01-20      2               1 1           2013-01-01      4               2 

the following sql statement doesn't work

  select distinct     ca1.staffid,     ca1.projectdepartment prev_deptid, ca1.startdate, ca1.enddate,      ca2.projectdepartment new_deptid, ca2.startdate, ca2.enddate  emp_hist ca1, emp_hist ca2      (ca1.staffid = ca2.staffid)      , ca1.startdate<>ca2.startdate     , ca1.enddate <>ca2.enddate     ,  ca2.startdate= dateadd(day, 1, ca1.enddate) 

for example, 2 records (true data) in 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 record using sql statement

your problem dates have time component. appear using sql server. can fix query doing this:

select ca1.staffid,        ca1.projectdepartment prev_deptid, ca1.startdate, ca1.enddate,         ca2.projectdepartment new_deptid, ca2.startdate, ca2.enddate emp_hist ca1 join      emp_hist ca2      on ca1.staffid = ca2.staffid ,         cast(ca1.startdate date) <> cast(ca2.startdate date) ,         cast(ca1.enddate date) <> cast(ca2.enddate date) ,         cast(ca2.startdate date) = dateadd(day, 1, cast(ca1.enddate date)); 

i replaced implicit join improved join syntax.