merging row on date

[chaoma]

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

 

[JarlH]

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.

 

[chaoma]

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.