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. 1 6568
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
-- This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: rc |
last post by:
Hi
I am kind of new to sql, so here goes my question
I have this sql update query
update set premeses=.
where (premeses is null)
Waht is wrong with this.
|
by: rrh |
last post by:
I am trying to update a field in one table with data from another
table. The problem I'm running into is I need to base the update on a
range of data in the 2nd table.
Table 1 has:
date field...
|
by: Nicolae Fieraru |
last post by:
Hi All,
I was trying to update a field in a table, based on the results from a
query.
The table to be updated is tblCustomers and the query is qrySelect. This
query has two parameters, provided...
|
by: Randy Harris |
last post by:
I imported records into a table, later found out that many of them had
trailing spaces in one of the fields. If I'd caught it sooner, I could have
trimmed the spaces before the import.
This...
|
by: N J |
last post by:
Hi,
I have code below that is used to add a new number to the txtTracking Field
based on what the user puts into the text box (txtTracking). What I would
like it to do is add whatever is already...
|
by: Henry Stockbridge |
last post by:
Hi,
I need a way of bypassing the Report_Close procedure (or come up with
another event to handle the Update Query.) Right now, if I set the
value of Report_NoData to Cancel=True, the...
|
by: Gilz |
last post by:
Hi
I was wondering if anyone could help i have picked this database up
from a
designer who has now left the company. The code is a couple of years
old.
On click of a button it open a...
|
by: Swinky |
last post by:
I am trying to make a combo box dependent on a text box. I cannot get
the combo box to pull the related data from the text box. I have been
all over this user group & have tried several versions...
|
by: kasinisak |
last post by:
I have created a Sub to export a query to a text file in the following steps:
1. Before the text file can be generated, certain checks must be made to ensure the correct result.
2. Generate a text...
|
by: beacon |
last post by:
Hi everybody,
I have an Employee table with the following:
Table - Employee
--------------------------
ID: AutoNum, PK
FName: Text
LName: Text
Status: Yes/No
|
by: lllomh |
last post by:
Define the method first
this.state = {
buttonBackgroundColor: 'green',
isBlinking: false, // A new status is added to identify whether the button is blinking or not
}
autoStart=()=>{
|
by: Mushico |
last post by:
How to calculate date of retirement from date of birth
|
by: DJRhino |
last post by:
Was curious if anyone else was having this same issue or not....
I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: tracyyun |
last post by:
Hello everyone,
I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
|
by: giovanniandrean |
last post by:
The energy model is structured as follows and uses excel sheets to give input data:
1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
|
by: NeoPa |
last post by:
Hello everyone.
I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report).
I know it can be done by selecting :...
|
by: NeoPa |
last post by:
Introduction
For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
|
by: Teri B |
last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course.
0ne-to-many. One course many roles.
Then I created a report based on the Course form and...
| |