Combining Records

Last Post 21 Feb 2007 04:28 AM by skt5000. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
skt5000
New Member
New Member

--
21 Feb 2007 03:46 AM
I almost have the query, but not quite. I have a table with data:

Employee EffectiveDate
1000 5/6/2002
1000 11/30/2002
1000 2/13/2006
1000 2/27/2006
1000 8/21/2006

What I need to do is create a new table with an EndDate column. Then EndDate is the EffectiveDate
of the next record, so I should end up with

Employee EffectiveDate EndDate
1000 5/6/2002 11/30/2002
1000 11/30/2002 02/13/2006
etc.

I get everything but the last record, or where there is no next record (e.g., employee 1001 has only in record with an effectivedate of '02/20/2007'). In this case I need to return an EndDate of null.

This Code


CREATE TABLE #t1 (
Employee varchar(10)
,Location varchar(10)
,EffectiveDate datetime
)
CREATE TABLE #t2 (
Recno int IDENTITY(1,1)
,Employee varchar(10)
,Location varchar(10)
,EffectiveDate datetime
)

CREATE TABLE #t3 (

Employee varchar(10)
,Location varchar(10)
,EffectiveDate datetime
,EndDate datetime)

INSERT INTO #t1 VALUES('1000','Orlando','05/06/2002')
INSERT INTO #t1 VALUES('1000','Orlando','11/30/2002')
INSERT INTO #t1 VALUES('1000','Orlando','02/13/2006')
INSERT INTO #t1 VALUES('1000','Orlando','02/27/2006')
INSERT INTO #t1 VALUES('1000','Orlando','08/21/2006')

INSERT INTO #t2
SELECT a.Employee,a.location,a.effectivedate
FROM #t1 a
ORDER BY 2,1


DECLARE @RecNo int
SET @RecNo = 1
DECLARE @maxrec int

SET @maxrec = (SELECT max(recno) from #t2)


WHILE @Recno <= @maxrec +1
BEGIN
INSERT INTO #t3
select b.Employee,b.location,a.effectivedate,b.effectivedate
from #t2 a
inner join #t2 b on
a.employee = b.employee
where @recno = a.recno
and a.recno = b.recno - 1

SET @recno = @recno + 1
END

produces this result
Employee Location EffectiveDate EndDate
1000 Orlando 2002-05-06 00:00:00.000 2002-11-30 00:00:00.000
1000 Orlando 2002-11-30 00:00:00.000 2006-02-13 00:00:00.000
1000 Orlando 2006-02-13 00:00:00.000 2006-02-27 00:00:00.000
1000 Orlando 2006-02-27 00:00:00.000 2006-08-21 00:00:00.000

and I need one more record

Employee Location EffectiveDate EndDate
1000 Orlando 2006-08-20 00:00:00.000 null

Thanks,

Jim


JHunter
New Member
New Member

--
21 Feb 2007 04:01 AM
I feel your're doing a bit more work that necessary...try this (i've assumed your table is called employees):


select a.employee, a.location, a.effectivedate, b.effectivedate
from employees a
left join employees b on b.employee = a.employee
and b.effectivedate = (
select min(effectivedate)
from employees t
where t.employee = a.employee
and t.effectivedate > a.effectivedate
);

Jamie
skt5000
New Member
New Member

--
21 Feb 2007 04:28 AM
Too bad I'm not paid by the hour. Your solution is brilliant, as always.

Thanks,

Jim
You are not authorized to post a reply.

Acceptable Use Policy