Here is the code that does the job. (MSSQLl2K)
It's not very pretty but it works.
/* Create table with demo info */
declare @memberinfo table(
memberId char(1),
startDate datetime,
endDate datetime
)
insert into @memberinfo(memberId, startDate, endDate)
values('A','07/31/2003','07/31/2004')
insert into @memberinfo(memberId, startDate, endDate)
values('A','07/31/2002','07/31/2003')
insert into @memberinfo(memberId, startDate, endDate)
values('A','07/31/2001','07/31/2002')
insert into @memberinfo(memberId, startDate, endDate)
values('B','08/01/2003','08/31/2004')
insert into @memberinfo(memberId, startDate, endDate)
values('B','08/01/2003','08/31/2004')
insert into @memberinfo(memberId, startDate, endDate)
values('B','07/31/2001','07/31/2002')
declare @holding table(
recid int identity(1,1),
memberid char(1),
startDate datetime,
endDate datetime
)
/* start processing */
declare @finalTable table(
recid int identity(1,1),
memberid char(1),
startDate datetime,
endDate datetime
)
insert into @holding(memberId, startDate, endDate)
select memberId, startDate, endDate
from @memberinfo
order by memberId, startDate
declare @mbrId char(1)
declare @strtDt datetime
declare @endDt datetime
DECLARE mycursor CURSOR FOR
SELECT memberId, startDate, endDate
FROM @holding
order by memberId, startDate
OPEN mycursor
FETCH NEXT FROM mycursor INTO @mbrId, @strtDt, @endDt
WHILE @@FETCH_STATUS = 0
BEGIN
if(@mbrId not in (select distinct memberId
from @finalTable))
begin
insert into @finalTable(memberId, startDate, endDate)
values(@mbrId, @strtDt, @endDt)
end
else
begin
if(@strtDt <= (select max(endDate)
from @finalTable
where memberId = @mbrId))
begin
update @finalTable
set endDate = @endDt
where memberId = @mbrId
and endDate = (select max(endDate)
from @finalTable
where memberId = @mbrId)
end
else
begin
insert into @finalTable(memberId, startDate, endDate)
values(@mbrId, @strtDt, @endDt)
end
end
fetch next from mycursor into @mbrId, @strtDt, @endDt
END
CLOSE mycursor
DEALLOCATE mycursor
select * from @finalTable
The output was:
1 A 2001-07-31 00:00:00.000 2004-07-31 00:00:00.000
2 B 2001-07-31 00:00:00.000 2002-07-31 00:00:00.000
3 B 2003-08-01 00:00:00.000 2004-08-31 00:00:00.000
So what do I get? :)
-----Original Message-----
From: Eric Creese [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 06, 2003 4:26 PM
To: CF-Talk
Subject: RE: SQL Challenge
Has to be in SQL. Will need to put it in a Stored Proc at some point.
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
