CODEWITHSUNDEEP
sqljoinmultiple-columns
Joe Yan
Joe Yan

Reputation: 2095

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

Upvotes: 2

Views: 8162

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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.

Upvotes: 1

ejlepoud
ejlepoud

Reputation: 1598

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

Upvotes: 1

Related Questions