Consolidate history rows

[MrPeds]

Hi,

I have a simple SQL Server 2008 table that contains history information relating to a salesPerson and their associated Office on given dates. The data is a bit like this:

ID_Salesman, ID_Office, StartDate, EndDate
1 2 2010-01-01 2010-01-05
1 2 2010-01-05 2010-01-10
1 3 2010-01-10 2010-01-15
1 2 2010-01-15 2010-01-30

As you can see, some of my rows overlap on the ID_Office front on certain dates. What I want to do is condense down the number of rows to 3 rows to be like this in a query:

ID_Salesman, ID_Office, StartDate, EndDate
1 2 2010-01-01 2010-01-10
1 3 2010-01-10 2010-01-15
1 2 2010-01-15 2010-01-30

So basically, when the StartDate is ordered , if the office id is the same as the previous row I want the end date to be amended.

For some reason am completely stuck on this one - a recursive query is needed?

Thanks,

MrPeds

 

[bborissov]

DECLARE @Test TABLE (ID_Salesman int, ID_Office int,   StartDate datetime,   EndDate datetime)
INSERT INTO @Test VALUES (1, 2, '2010-01-01','2010-01-05')
INSERT INTO @Test VALUES (1, 2, '2010-01-05','2010-01-10')
INSERT INTO @Test VALUES (1, 3, '2010-01-10','2010-01-15')
INSERT INTO @Test VALUES (1, 2, '2010-01-15','2010-01-30')


SELECT Test.ID_Salesman,
       Test.ID_Office,
       MIN(Test.StartDate) AS StartDate,
       ISNULL(Test1.EndDate, Test.EndDate) AS EndDate
FROM @Test Test 
LEFT JOIN @Test Test1 ON Test.ID_Office   = Test1.ID_Office   AND
                         Test.ID_Salesman = Test1.ID_Salesman AND
                         Test.EndDate     = Test1.StartDate
GROUP BY Test.ID_Salesman,
         Test.ID_Office,
         ISNULL(Test1.EndDate, Test.EndDate)

NOT WELL TESTED!!!

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[drlex]

I had a similar requirement with absence periods; I found good information here -

http://www.dbforums.com/ansi-sql/969799-help-contiguous-date-range-query.html

soi là, soi carré

 

[MrPeds]

Thanks guys, I will try both these out shortly and let you know if I get stuck further.

Regards.

MrPeds