Help, please. I am trying to update a
table with this structre:
CREATE TABLE Queue (PropID int, EffDate smalldatetime,
TxnAmt int)
INSERT Queue (PropID) SELECT 1
INSERT Queue (PropID) SELECT 2
INSERT Queue (PropID) SELECT 3
....from this table...
CREATE TABLE Txns (PropID int, TxnDate smalldatetime,
TxnType char(1), TxnAmt int)
INSERT Txns SELECT 1 '20000201', 'B', 100000
INSERT Txns SELECT 1 '20020515', 'B', 110000
INSERT Txns SELECT 1 '20020515', 'A', 120000
INSERT Txns SELECT 1 '20020615', 'c', 130000
....only certain txn types are okay, and they have an order
of preference...
CREATE TABLE GoodTxnTypes (GoodTxnType char(1), Pref)
INSERT GoodTxnTypes SELECT 'A', 1
INSERT GoodTxnTypes SELECT 'B', 2
The idea is to fill in the NULL fields in the Queue table,
according to a rule -- the transaction must be the latest
transaction within a date window, it must be one of the good
txn types, and if there are two txns on that date, choose
the txn by the preferred txn type (A is preferred over B,
according to the field Pref).
If the time window were 20020101 to 20030101, the txn
selected to update the Queue table would be this one:
INSERT Txns SELECT 1 '20020515', 'A', 120000 -- there are
two in the time window that are type A or B; they are
both on the same day, so the 'A' is preferred.
If the time window were 20000101 to 20010101, this would
be selected because it is the only A or B type txn in
the interval:
INSERT Txns SELECT 1 '20000201', 'B', 100000
I'm looking for a statement that starts...
UPDATE Queue SET EffDate = ...., TxnAmt = .... (EffDate,
in this table, is the same as TxnDate in the Txn table).
Assume we have @FirstDate and @LastDate available.
Help, please. I'm getting stuck with (a) a sub-query to
find the relevant Txn records, and (b) another sub-query
within that to find the MAX(TxnDate) within the time
window. Filtering the Txn records on the basis of the
GoodTxnTypes table is easy, as is ordering what is returned.
But I'm having trouble joining the sub-queries back to the
Queue table on the basis of PropId.