working with a date range to return the correct data

Question

Here is a problem which involves displaying the correct address for a person based on dates.  The final output is to show last name, payment details and people's addresses on the date they paid their bill.  Not sure how to approach this one.  Here is the test code:

declare @Person table (PersonID int, LastName nvarchar(20));
insert into @Person values
(1, 'Olson'),
(2, 'Johnson'),
(3, 'Brown');

declare @AddressHistory table (PersonID int, AddressID int, StreetAddress nvarchar(30), City nvarchar(20), State nvarchar(2), EffectiveDate date);
insert into @AddressHistory values
(1, 100, '1 Solo Drive', 'Billings', 'MT', '1/1/2011'),
(1, 101, '11 Solitary Road', 'Missoula', 'MT', '9/1/2011'),
(1, 105, '111 One Way', 'Helena', 'MT', '3/1/2012'),
(2, 102, '2 Duo Drive', 'Madison', 'WI', '2/2/2002'),
(2, 104, '222 Two Avenue', 'Milwaukee', 'WI', '2/22/2012'),
(3, 103, '3 Triplets Way', 'Kalamazoo', 'MI', '3/3/2003');

declare @PaymentHistory table (PersonID int, AmountPaid decimal(5,2) default 10.00, DatePaid date);
insert into @PaymentHistory (PersonID, DatePaid) values
(1, '1/1/2011'),
(1, '1/7/2011'),
(1, '8/31/2011'),
(1, '9/1/2011'),
(1, '9/2/2011'),
(1, '2/29/2012'),
(1, '3/1/2012'),
(1, '3/2/2012'),
(1, '3/30/2012'),
(2, '2/2/2002'),
(2, '2/3/2002'),
(2, '2/21/2012'),
(2, '2/22/2012'),
(2, '2/23/2012'),
(3, '3/2/2003'),  --this one is before address date existed (on purpose)
(3, '3/3/2003'),
(3, '3/4/2003'),
(3, '3/5/2003');

select * from @Person;
select * from @AddressHistory;
select * from @PaymentHistory;

--format of output...except need actual address info in the last 3 columns which reflects the person's address when they made their payment
select p.PersonID, p.LastName, ph.AmountPaid, ph.DatePaid, null as StreetAddress, null as City, null as State
from @Person p
inner join @PaymentHistory ph
on p.PersonID = ph.PersonID;

Answer 1

Here's one method.  Address columns will be NULL when no address is effective on a given payment date:

SELECT 
	p.PersonID
	, p.LastName
	, ph.AmountPaid
	, ph.DatePaid
	, ah.StreetAddress
	, ah.City
	, ah.State
FROM @Person p
INNER JOIN @PaymentHistory AS ph
	ON p.PersonID = ph.PersonID
OUTER APPLY(
	SELECT TOP (1)
		StreetAddress
		,City
		,State
	FROM @AddressHistory AS ah
	WHERE 
		ah.PersonID = p.PersonID
		AND ah.EffectiveDate <= ph.DatePaid
	ORDER BY ah.EffectiveDate DESC) AS ah;

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 2

select p.PersonID, p.LastName, ph.AmountPaid, ph.DatePaid, a.StreetAddress, a.City, a.State
from @Person p
inner join @PaymentHistory ph on p.PersonID = ph.PersonID
outer apply (select top 1 * from @AddressHistory ah where ah.PersonID = p.PersonID and ah.EffectiveDate <= ph.DatePaid order by ah.EffectiveDate desc) as a;

Tom