Linking records based on date ranges

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 2008 Forums

Transact-SQL (2008)

Linking records based on date ranges

Author

Topic

ch9862
Yak Posting Veteran

76 Posts

Posted - 2012-05-14 : 15:17:15

I need some help merging two tables based on date ranges.

There are two tables describing entities, one with "main" properties, which rarely change and another with "secondary" properties, which change more frequently.
Both tables have 3 columns in common - ID, StartDate and EndDate. ID identifies entity, StartDate and EndDate define time period for which a particular set of properties is valid.
I need to extract some properties from each table and merge it into one - the issue I'm struggling with is that StartDate and EndDate values are different in both tables.

As an example:

declare @tMain table (
	 ID int
	,StartDate datetime
	,EndDate datetime
	,Name varchar(10)
)
insert	@tMain
select 1, '1/1/2000', '1/1/2005', 'Mike' union all
select 1, '1/2/2005', '12/31/9999', 'Chris' union all
select 2, '1/1/2000', '12/31/9999', 'Angie'

declare @tSecondary table (
	 ID int
	,StartDate datetime
	,EndDate datetime
	,Value int
	,Location varchar(4)
)
insert	@tSecondary
select 1, '1/1/2000', '1/1/2003', 200, 'MD' union all
select 1, '1/2/2003', '1/1/2006', 250, 'WV' union all
select 1, '1/2/2006', '12/31/9999', 190, 'VA' union all
select 2, '1/1/2000', '12/31/9999', 300, 'NY'

What I want to produce is:

/*
ID          StartDate               EndDate                 Name       Value       Location
----------- ----------------------- ----------------------- ---------- ----------- --------
1           2000-01-01 00:00:00.000 2003-01-01 00:00:00.000 Mike       200         MD
1           2003-01-02 00:00:00.000 2005-01-01 00:00:00.000 Mike       250         WV
1           2005-01-02 00:00:00.000 2006-01-01 00:00:00.000 Chris      250         WV
1           2006-01-02 00:00:00.000 9999-12-31 00:00:00.000 Chris      190         VA
2           2000-01-01 00:00:00.000 9999-12-31 00:00:00.000 Angie      300         NY
*/

I'm guessing the whole thing will have to be done in a stored procedure. It seems to me that I would need to start with creating a list of all distinct start dates for each ID. Once I have that, I'd have to fill in the end dates to match. How do I do that?

Thanks in advance for ideas!

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-05-14 : 19:00:18

[CODE]select
m.id,
case when m.StartDate > s.StartDate then m.StartDate else s.StartDate end StartDate,
case when m.EndDate < s.EndDate then m.EndDate else s.EndDate end EndDate,
m.Name,
s.Value,
s.Location
from
@tMain m
inner join
@tSecondary s
on m.ID = s.ID
and m.StartDate <= s.EndDate
and m.EndDate >= s.StartDate
order by
ID,
StartDate,
EndDate[/CODE]

=================================================
There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE)

ch9862
Yak Posting Veteran

76 Posts

Posted - 2012-05-15 : 08:45:53

Amazing - thanks so much!

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-05-15 : 11:13:05

Mi gusto!

=================================================
There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE)

ch9862
Yak Posting Veteran

76 Posts

Posted - 2012-06-15 : 14:50:13

A bit of a curve ball - I've found instances where minimum dates in both tables (for given entity) are different.
E.g.

declare @tMain table (
	 ID int
	,StartDate datetime
	,EndDate datetime
	,Name varchar(10)
)
insert	@tMain
select 1, '1/1/1980', '1/1/2005', 'Mike' union all
select 1, '1/2/2005', '12/31/9999', 'Chris' union all
select 2, '1/1/1999', '12/31/9999', 'Angie'

declare @tSecondary table (
	 ID int
	,StartDate datetime
	,EndDate datetime
	,Value int
	,Location varchar(4)
)
insert	@tSecondary
select 1, '1/1/2000', '1/1/2003', 200, 'MD' union all
select 1, '1/2/2003', '1/1/2006', 250, 'WV' union all
select 1, '1/2/2006', '12/31/9999', 190, 'VA' union all
select 2, '1/1/2000', '12/31/9999', 300, 'NY'

The query returns records for date ranges with values in both tables, but how would I go about adding range for which values are only in the "main" table?

For the sample data above I'd like to return:

/*
ID          StartDate               EndDate                 Name       Value       Location
----------- ----------------------- ----------------------- ---------- ----------- --------
1           1980-01-01 00:00:00.000 1999-12-31 00:00:00.000 Mike       NULL        NULL
1           2000-01-01 00:00:00.000 2003-01-01 00:00:00.000 Mike       200         MD
1           2003-01-02 00:00:00.000 2005-01-01 00:00:00.000 Mike       250         WV
1           2005-01-02 00:00:00.000 2006-01-01 00:00:00.000 Chris      250         WV
1           2006-01-02 00:00:00.000 9999-12-31 00:00:00.000 Chris      190         VA
2           1999-01-01 00:00:00.000 1999-12-31 00:00:00.000 Angie      NULL        NULL
2           2000-01-01 00:00:00.000 9999-12-31 00:00:00.000 Angie      300         NY
*/

Any ideas? Maybe it should be a separate query retrieving only those missing ranges?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources