Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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

Query for record that is effective as of a certain date

Show all headers | 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