Hi, I hope someone can help here.

Here is a sample data:

ID   StartDate     EndDate      ZipCode
1    20080101      20080128     90815
1    20080129      20080315     90815
1    20080316      Null         90819
2    20080201      20080415     90801
2    20080416      20080601     90802
2    20080602      20080809     90801

Here is the query to help you generate this data:

select '1' as [id], '20080101' as StartDate, '20080128' as EndDate, '90815' as ZipCode into Table1
union all
select '1','20080129','20080315','90815'
union all
select '1','20080316', Null, '90819'
union all
select '2','20080201','20080415','90801'
union all
select '2','20080416','20080601','90802'
union all
select '2', '20080602', '20080809','90801'


I want my result to look like this

ID   StartDate     EndDate      ZipCode
1    20080101      20080315     90815
1    20080315      Null         90819
2    20080201      20080415     90801
2    20080416      20080601     90802  (change in zip code)
2    20080602      20080809     90801   (move back to old address)

I tried to use this query, but it doesn't work:

SELECT    [ID],
    MIN(StartDate) AS StartDate,
    MAX(EndDate) AS EndDate,
    ZipCode
FROM    Table1
GROUP BY [ID], ZipCode
ORDER BY [ID], ZipCode

My result come out to be:

ID   StartDate EndDate  ZipCode
---- --------- -------- -------
1    20080101  20080315 90815
1    20080316  NULL     90819
2    20080201  20080809 90801  (wrong result here)
2    20080416  20080601 90802

I am using Microsoft SQL Server 2000.

Thank you for your assistance.

Chaoma
 

A simple group by with max/min will not work. Your query must also verify that the periods are directly subsequent to each other before merging two periods (or 3 or 4 or 5...)

Do you have to store the data like this? I'd rather store it the way you want to return it, i.e. update existing rows with merged periods instead of inserting new rows.
 

I have to merge this data to another table.  This date must fall within the date on another tables.  What I meant is if client changed address, did something else happen in the same period?

Therefore, I think the data need to be group first before I can merge with another table.  It's not matter of storing.  Do you have a way to check that the data subsequent to each other before merging?

Thanks.