SQL Distinct Question
SQL Distinct Question
(OP)
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
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
RE: SQL Distinct Question
CODE
, 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
RE: SQL Distinct Question
T1.FirstName,
T1.LastName,
T1.SSN,
T1.Address,
T1.City,
T1.ST,
T1.ZIP,
T2.Date,
T2.Rep
RE: SQL Distinct Question
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: SQL Distinct Question
CODE
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.
RE: SQL Distinct Question
CODE
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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: SQL Distinct Question
RE: SQL Distinct Question
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
CODE
T1.SSN,
T1.FIRSTNAME,
T1.LASTNAME,
T1.ADDR,
T1.CITY,
T1.ST,
T1.ZIP
--VISITOR DATE DETAIL TABLE
T2.ACCTNO,
T2.REP,
T2.APPT_DATE
RE: SQL Distinct Question
RE: SQL Distinct Question
So a 5 time visitor still appears multiple times in the list.
RE: SQL Distinct Question
Finally taking only one SSN (T1.SSN) where T1.ACCTNO = T2.ACCTNO
RE: SQL Distinct Question
CODE
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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?