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 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply