Path: csiph.com!x330-a1.tempe.blueboxinc.net!feeder1.hal-mli.net!nx01.iad01.newshosting.com!newshosting.com!news-out.readnews.com!transit3.readnews.com!postnews.google.com!q20g2000vbx.googlegroups.com!not-for-mail From: BobRoyAce Newsgroups: comp.databases.ms-sqlserver Subject: Query for record that is effective as of a certain date Date: Mon, 16 May 2011 09:49:41 -0700 (PDT) Organization: http://groups.google.com Lines: 36 Message-ID: <2d2ba1e2-e68b-4292-8bac-e8af30fe3ebb@q20g2000vbx.googlegroups.com> NNTP-Posting-Host: 71.233.137.124 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 X-Trace: posting.google.com 1305564585 18602 127.0.0.1 (16 May 2011 16:49:45 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Mon, 16 May 2011 16:49:45 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: q20g2000vbx.googlegroups.com; posting-host=71.233.137.124; posting-account=r5en3QoAAAAe1xXGPLgZeWvDc3VNF-dB User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_7) AppleWebKit/534.24 (KHTML, like Gecko) Chrome/11.0.696.57 Safari/534.24,gzip(gfe) Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:333 Let's say that I have three tables Loans ===== pkLoanID int LoanNum varchar(20) LoanStatuses ========== pkLoanStatusID int LoanStatusDesc varchar(50) LoanStatusChanges =============== fkLoanID int fkLoanStatusID int EffectiveDate datetime I want to run a query that will pull, for each Loan, the LoanNum and the LoanStatusDesc that corresponds to the status that was effective as of a particular date. So, for example, let's suppose that LoanNum A123456 (where pkLoanID = 1) has the following records in LoanStatusChanges: 1 1 1/1/2011 1 2 1/15/2011 1 3 3/14/2011 1 4 5/1/2011 If I run the query, wanting to know the status as of 4/31/2011, I would want to grab the StatusDesc for the record with a pkLoanStatus of 3. How could I construct such a query? Is this something I could do with Select...Over...Partition By? I am not very familiar with that construct, but it seems that it might be relevant.