Joe Yan Joe Yan - 6 days ago 7
SQL Question

SQL join two record into one row with multiple column

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

Answer

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.

Comments