Update query with sub-queries to find the values

[Jim Geissman]

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.

[David Portas]

Thanks for posting the DDL. Unfortunately you didn't include any primary
keys so I've made the following assumptions:

CREATE TABLE Queue (propid INTEGER PRIMARY KEY, effdate DATETIME NULL,
txnamt INTEGER NULL)

CREATE TABLE Txns (propid INTEGER, txndate DATETIME, txntype CHAR(1), txnamt
INTEGER NOT NULL, PRIMARY KEY (propid, txndate, txntype))

CREATE TABLE GoodTxnTypes (goodtxntype CHAR(1) PRIMARY KEY, pref INTEGER NOT
NULL UNIQUE)

I suggest that you create a view rather than try to UPDATE the Queue table.
To do this, create a single-row table to hold the required date range:

CREATE TABLE TxnDateRange (startdate DATETIME NOT NULL, enddate DATETIME NOT
NULL, CHECK (startdate<=enddate), dummycol INTEGER PRIMARY KEY DEFAULT 0
CHECK (dummycol=0))

INSERT INTO TxnDateRange (startdate, enddate) VALUES ('20020101',
'20030101')

Now you can create a view like this:

CREATE VIEW Txn_Queue (propid, effdate, txnamt)
AS
SELECT T.propid, T.txndate , T.txnamt
FROM Txns AS T
JOIN GoodTxnTypes AS G
ON T.txntype = G.goodtxntype
JOIN
(SELECT T.propid,
MAX(T.txndate), MIN(G.pref)
FROM Txns AS T
JOIN TxnDateRange AS D
ON T.txndate BETWEEN D.startdate AND D.enddate
JOIN GoodTxnTypes AS G
ON T.txntype = G.goodtxntype
GROUP BY propid) AS M (propid, txndate, pref)
ON G.pref = M.pref
AND T.txndate = M.txndate

This view is the equivalent of your Queue table. Note that joins often
perform better than subqueries and can usually serve the same purpose.

If you really want to update the Queue table, do it from the above view:

UPDATE Queue
SET effdate =
(SELECT effdate
FROM Txn_Queue
WHERE propid = Queue.propid),
txnamt =
(SELECT txnamt
FROM Txn_Queue
WHERE propid = Queue.propid)

--
David Portas
------------
Please reply only to the newsgroup
--