February 25, 2013 at 9:10 am
I have the following two queries:
SELECT ID, Color, Date1, Standing, Name, Date2
FROM VW_Tbl1
WHERE Date1 < '2012-01-01 00:00:00.000' AND (Standing='Good' OR Standing='Bad')
GROUP BY ID, Color, Date1, Standing, Name, Date2
ORDER BY ID
SELECT ID, MAX(ENTRY_TIMESTAMP) AS Date2, ActionType
FROM VW_Tbl2
WHERE ActionType='REO'
GROUP BY ID
ORDER BY ID
The first query returns the following results:
IDColor Date1 StandingName
101Blue 1/1/2013Good Jim
102Red 1/2/2013Bad Donald
103Green 2/1/2013Bad Burt
104Purple 2/2/2013Good Hogan
105Yellow 2/2/2013Good Klink
106Blue 2/20/2013Bad Schultz
The second query returns the following results:
IDDate2 ActionType
1002/25/2013REO
1012/23/2013REO
1022/18/2013REO
1032/25/2013REO
1042/21/2013REO
1052/5/2013REO
1072/20/2013REO
1082/21/2013REO
1092/21/2013REO
1102/25/2013REO
I would like to lookup the Date2 data based on the ID field, and populate the first query results with the desired data. The results would be:
IDColor Date1 Standing NameDate2
101Blue 1/1/2013 Good Jim2/23/2013
102Red 1/2/2013 Bad Donald2/18/2013
103Green 2/1/2013 Bad Burt2/25/2013
104Purple 2/2/2013 Good Hogan2/21/2013
105Yellow 2/2/2013 Good Klink2/5/2013
106Blue 2/20/2013 Bad Schultz
Currently, I get the above results by importing the data into Excel and using the VLOOKUP function.
Any suggestions, tips, and/or advice is greatly appreciated. Also, if there are any other threads that already answer this, a link would be very helpful.
If any of this is not clear, please ask questions and I'll respond as quickly and thoroughly as possible.
Thanks,
J
February 25, 2013 at 9:15 am
I am working on reorganizing the above post based on the guidelines provided by Jeff Moden in his post "Forum Etiquette: How to post data/code on a forum to get the best help" located at http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D .
It's new to me so it may take a while, so your patience is greatly appreciated.
Thanks,
J
February 25, 2013 at 9:27 am
Guess you're after this: -
SELECT a.ID, Color, Date1, Standing, Name, b.Date2
FROM VW_Tbl1 a
INNER JOIN (SELECT ID, MAX(ENTRY_TIMESTAMP) AS Date2
FROM VW_Tbl2
WHERE ActionType='REO'
GROUP BY ID) b ON a.ID = b.ID
WHERE Date1 < '2012-01-01 00:00:00.000' AND (Standing='Good' OR Standing='Bad')
GROUP BY a.ID, Color, Date1, Standing, Name
ORDER BY a.ID;
I worry about your groupings on "VW_Tbl1", since there is no aggregation I've got to assume that you're de-duplicating. I'd look in to the cause of the duplication and fix that, otherwise you'll give yourself a headache later down the line.
Secondly, I'd read up on joins --> http://msdn.microsoft.com/en-us/library/ms191517(v=sql.105).aspx
February 25, 2013 at 9:44 am
Thank you very much for your help. I changed the join from INNER to LEFT so that I get the total results from the first query while populating the Date2 data from the second query, and it works exactly as I needed.
Also, thank you for the link to joins information, I plan to read it immediately.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply