I have the 3 raw data tables below, and would like to extract some
data. My current query is really cumbersome, does anyone know a better
way to get all the info (attach the right state to the lowest zip code)
in one step? Thanks a lot! Here are the details.
- Ideal Output, for each person , keep
(1) person_id (From Table A)
(2) The earliest open_date of accounts starting with 2 (From Table A)
(3) ssn (From Table B)
(4) Zip and state info (From Table C), but only keep the lowest zip and
state.
-Table A -
person_idaccountopen_date
10001220000015/15/2003
10001220000026/20/2004
10001300000012/2/2002
10002220000038/12/2004
10002220000049/15/2004
10002300000022/16/2005
-Table B -
person_idzip state
1000111111AA
1000122222CC
1000233333BB
--Table C -
person_idssn
100011234567
100022345678
-Ideal Output-
person_idmin_openssnzipstate
100015/15/2003123456711111AA
100028/12/2004234567833333BB
Here is what I did:
Select a.person_id, min(a.open_date), b.ssn, min(c.zip) as zip
,cast (0 as varchar) as state
Into output
>From TableA as a
Join TableB as b
on a.person_id=b.person_id
Join TableC as c
on a.person_id=c.person_id
Where a.account like '2%'
Group by a.person_id, b.ssn
Order by a.person_id
Update output
Set output.state=b.state
>From output as a
Join TableC as b
on a.person_id=b.person_id
and a.zip=b.zip(rong.guo@.gmail.com) writes:
> I have the 3 raw data tables below, and would like to extract some
> data. My current query is really cumbersome, does anyone know a better
> way to get all the info (attach the right state to the lowest zip code)
> in one step? Thanks a lot! Here are the details.
> - Ideal Output, for each person , keep
> (1) person_id (From Table A)
> (2) The earliest open_date of accounts starting with 2 (From Table A)
> (3) ssn (From Table B)
> (4) Zip and state info (From Table C), but only keep the lowest zip and
> state.
For these kind of these questions, it's always recommendable to post:
o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.
This makes it possible to easily copy and paste into Query Analyzer to
develop a tested query. Since your sample data was not in this form,
this query is untested:
SELECT a1.person_id, a1.min_open, c.ssn, a1.min_zip, b.state
FROM (SELECT a.person_id, min_open = MIN(a.open_date),
min_zip = MIN(b.zip)
FROM table_a a
JOIN table_b b ON a.person_id = b_person_id
GROUP BY a.person_id) AS a1
JOIN table_b b ON a1.person_id = b.person_id
AND a1.min_zip = b.zip
JOIN table_c c ON a1.person_id = c.person_id
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, Erland.
Because I am not familiar with CREATE and INSERT, I didn't use them
this time. Sorry for the inconvenience. I will certainly try it next
time.
Thanks again!
