Groups > comp.databases.ms-sqlserver > #333

Query for record that is effective as of a certain date

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 <broy@omegasoftwareinc.com>
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> (permalink)
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

Show key headers only | View raw

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.

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

Query for record that is effective as of a certain date BobRoyAce <broy@omegasoftwareinc.com> - 2011-05-16 09:49 -0700
  Re: Query for record that is effective as of a certain date Erland Sommarskog <esquel@sommarskog.se> - 2011-05-16 22:29 +0200
    Re: Query for record that is effective as of a certain date BobRoyAce <broy@omegasoftwareinc.com> - 2011-05-17 07:34 -0700

csiph-web