Compare two most recent columns for same ID

Question

I have a table for persons’ addresses. The Class column tells whether it is a home (H), postal (P) or internal (I) address. Each time a person updates the address, the date is stored as well.

The internal address is used by my company when a correspondence is returned to us due to incorrect addresses, so we update that to our address until we can obtain new contact details.

I’d like to know which persons have changed their address to a postal or home address with an effective date after the effective date of the internal address.

Here’s an example of what the table looks like:

| **PersonID**| **Class** | **EffDate** | **Line1**
| 1           | H         | 12/01/2010  | 31 Academy Avenue 
| 1           | H         | 13/09/2010  | 433 Hillcrest Drive 
| 1           | I         | 26/10/2015  | 1 Bond Circle
| 2           | H         | 17/12/2012  | 761 Circle St. 
| 2           | H         | 12/11/2013  | 597 Elm Lane 
| 2           | I         | 1/10/2015   | 1 Bond Circle
| 2           | H         | 6/12/2016   | 8332 Mountainview St. 
| 3           | P         | 27/09/2010  | 8 Bow Ridge Lane 
| 3           | H         | 6/12/2010   | 22 Shady St. 
| 3           | I         | 7/12/2015   | 1 Bond Circle
| 3           | H         | 8/12/2016   | 7423 Rockcrest Ave. 
| 4           | P         | 9/12/2015   | 888 N. Shady Street 
| 4           | I         | 10/12/2016  | 1 Bond Circle

I'd like the query to only return:

| **PersonID**| **Class** | **EffDate** | **Line1**
| 2           | H         | 6/12/2016   | 8332 Mountainview St.
| 3           | H         | 8/12/2016   | 7423 Rockcrest Ave.

Any ideas? I'm using SQL Server 2012.

score 3 · Accepted Answer · answered Nov 22 '16 at 02:01

Here is one method:

select t.*
from t
where t.class in ('H', 'P') and
      t.effdate > (select max(t2.effdate)
                   from t t2
                   where t2.class = 'I' and t2.personId = t.personId
                  );

Another method that uses window functions:

select t.*
from (select t.*,
              max(case when class = 'I' then date end) over (partition by personid) as max_idate
      from t
     ) t
where t.class in ('H', 'P') and
      t.date > t.maxidate;

Compare two most recent columns for same ID

1 Answers1