none
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;

    vendredi 13 avril 2012 01:30

Réponses

  • 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/

    • Proposé comme réponse Naomi NModerator vendredi 13 avril 2012 02:20
    • Marqué comme réponse Knot vendredi 13 avril 2012 02:57
    vendredi 13 avril 2012 01:52

Toutes les réponses

  • 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/

    • Proposé comme réponse Naomi NModerator vendredi 13 avril 2012 02:20
    • Marqué comme réponse Knot vendredi 13 avril 2012 02:57
    vendredi 13 avril 2012 01:52
  • 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
    vendredi 13 avril 2012 01:59