merge data with date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

merge data with date

Author

Topic

ChaoMa
Starting Member

2 Posts

Posted - 2009-01-07 : 14:19:30

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

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-07 : 16:35:38

Because you are looking for Min(startdate) and Max(Enddate) from Id=2 and Zipcode='90801' Which will be (2,90801,20080201,20080809) from

SELECT [ID],Zipcode,
MIN(StartDate) AS StartDate,
MAX(EndDate) AS EndDate
FROM (select '1' as [id], '20080101' as StartDate, '20080128' as EndDate, '90815' as ZipCode
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')t
GROUP BY [ID], ZipCode
ORDER BY [ID], ZipCode

ChaoMa
Starting Member

2 Posts

Posted - 2009-01-07 : 18:18:01

Sodeep, thank you very much. But...

Your query and mine is exactly alike.

For ID=2, the min=20080201, the max=20080809 for ZipCode=90801.

However, I forgot to mention that the data have to be continuous.

ID=2 moved from 90801 to 90802 on 20080416, then moved back to 90801 on 20080602.

The result for ID =2 in a continous row would be:

2 20080201 20080415 90801 (row1)
2 20080416 20080601 90802 (row2)
2 20080602 20080809 90801 (row3)

Your query would return 1 row for 90801 (min=20080201 and max=20080809). This would imply that ID 2 lived on 90801 from 20080201 to 20080809. This isn't true, because he lived on 90802 between those period.

I hope you can help me here.

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources