SQL Distinct Question

[epoh]

I am working on a query that has the following fields

T1.FirstName, T1.LastName, T1.SSN, T1.Date, T1.Address, T1.City, T1.ST, T1.ZIP, T2.Rep

sample data
T1.FirstName, T1.LastName, T1.SSN, T1.Date, T1.Address, T1.City, T1.ST, T1.ZIP, T2.Rep
John,Doe,500-22-1234,2010-12-10,123 N Main,Anytown,US,12345,1
John,Doe,500-22-1234,2010-11-10,123 N Main,Anytown,US,12345,1
John,Doe,500-22-1234,2008-08-13,222 Old Address Rd,Oldtown,MD,12345,1
Jane,Doe,511-33-4321,2010-12-10,123 N Main,Anytown,US,12345,1
James,Smith,412-11-7894,2009-11-30,123 N Broadway,Anytown,US,12345,1
James,Smith,412-11-7894,2009-11-30,123 N Broadway,Anytown,US,12345,1
Jimmy,Staton,510-44-4567,2010-01-20,456 N Broadway,Anytown,US,12345,1

As seen in the data sample there are multiple SSNs but really only one customer.

How can I sort based on newest date (to get the most recent address) and then by unique SSN to get the desired data below?

Desired sample data:
T1.FirstName, T1.LastName, T1.SSN, T1.Date, T1.Address, T1.City, T1.ST, T1.ZIP, T2.Rep
John,Doe,500-22-1234,2010-12-10,123 N Main,Anytown,US,12345,1
Jane,Doe,511-33-4321,2010-12-10,123 N Main,Anytown,US,12345,1
James,Smith,412-11-7894,2009-11-30,123 N Broadway,Anytown,US,12345,1
Jimmy,Staton,510-44-4567,2010-01-20,456 N Broadway,Anytown,US,12345,1

Thanks

 

[r937]

SELECT T1.FirstName
     , T1.LastName
     , T1.SSN
     , T1.Date
     , T1.Address
     , T1.City
     , T1.ST
     , T1.ZIP
     , T2.Rep
  FROM ( SELECT SSN
              , MAX(Date) AS latest
           FROM daTable
         GROUP
             BY SSN ) AS s
INNER
  JOIN daTable AS T1
    ON T1.SSN = s.SSN
   AND T1.Date = s.latest

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[epoh]

Oops, I goofed.

T1.FirstName,
T1.LastName,
T1.SSN,
T1.Address,
T1.City,
T1.ST,
T1.ZIP,
T2.Date,
T2.Rep

 

[PHV]

What is your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[epoh]

--CUSTOMER INFO TABLE
SELECT 
T1.ACCTNO,
T1.FIRSTNAME,
T1.LASTNAME,
T1.ADDR,
T1.CITY,
T1.ST,
T1.ZIP,
T1.SSN,
--VISITOR DATE DETAIL TABLE
T2.ACCTNO,
T2.REP,
T2.APPT_DATE

FROM CUST_INFO T1, VISIT_DETAIL T2
WHERE T1.ACCTNO = T2.ACCTNO
ORDER BY T2.APPT_DATE DESC

What is throwing me off is getting DISTINCT SSNs using the information provided in the last appointment date "APPT_DATE". Because it is possible for the address to change from appointment to appointment I want to use the latest appointment date for the customer based on their unique SSN. Note: SSN is more unique than ACCTNO.

 

[PHV]

--CUSTOMER INFO TABLE
SELECT 
T1.ACCTNO,
T1.FIRSTNAME,
T1.LASTNAME,
T1.ADDR,
T1.CITY,
T1.ST,
T1.ZIP,
T1.SSN,
--VISITOR DATE DETAIL TABLE
T2.ACCTNO,
T2.REP,
T2.APPT_DATE

FROM CUST_INFO T1 
INNER JOIN VISIT_DETAIL T2 ON T1.ACCTNO = T2.ACCTNO
INNER JOIN (
SELECT ACCTNO,MAX(APPT_DATE) AS LastDate FROM VISIT_DETAIL GROUP BY ACCTNO
) T3 ON T2.ACCTNO = T3.ACCTNO AND T2.APPT_DATE = T3.LastDate
ORDER BY T2.APPT_DATE DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[epoh]

I will try this tomorrow when I'm back at work

 

[epoh]

Okay, this is closer but not quite there.

I'm trying to get a distinct/unique SSN based on the following:

Latest VISIT_DETAIL.APPT_DATE AND VISIT_DETAIL.ACCT_NO
&
T2.ACCTNO = T1.ACCTNO
&
the DISTINCT T1.SSN that is in the same row as T1.ACCTNO

[COLOR=blue]T1.ACCTNO,[/color]
[COLOR=blue]T1.SSN,[/color]
T1.FIRSTNAME,
T1.LASTNAME,
T1.ADDR,
T1.CITY,
T1.ST,
T1.ZIP
--VISITOR DATE DETAIL TABLE
T2.ACCTNO,
T2.REP,
T2.APPT_DATE

 

[PHV]

And what is wrong with my suggestion ?

 

[epoh]

Nothing. After further review I just found multiple entries for the same customer. That's because the ACCTNO changes with each visit. I overlooked that.

So a 5 time visitor still appears multiple times in the list.

 

[epoh]

What I want is to use the current address (T1.ADDR) that was entered when the customer visits using the latest APPT/VISIT date (T2.APPT_DATE) using the subquery you suggested.

Finally taking only one SSN (T1.SSN) where T1.ACCTNO = T2.ACCTNO

 

[PHV]

Perhaps this ?

--CUSTOMER INFO TABLE
SELECT
T1.ACCTNO,
T1.FIRSTNAME,
T1.LASTNAME,
T1.ADDR,
T1.CITY,
T1.ST,
T1.ZIP,
T1.SSN,
--VISITOR DATE DETAIL TABLE
T2.ACCTNO,
T2.REP,
T2.APPT_DATE
FROM CUST_INFO T1
INNER JOIN VISIT_DETAIL T2 ON T1.ACCTNO = T2.ACCTNO
INNER JOIN (
SELECT C.SSN,MAX(V.APPT_DATE) AS LastDate FROM CUST_INFO C INNER JOIN VISIT_DETAIL V ON C.ACCTNO=V.ACCTNO GROUP BY C.SSN
) T3 ON T2.APPT_DATE = T3.LastDate
WHERE T1.SSN = T3.SSN
ORDER BY T2.APPT_DATE DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886