Help with a complex UPDATE query

[Slower Than You]

Well, I think it's complex anyway -- you might not :)

TableDef:
CREATE TABLE CustTransaction s (
TransactionKey int IDENTITY(1,1) NOT NULL,
CustomerID int,
AmountSpent float,
CustSelected bit default 0);

TransactionKey is the primary key, CustomerID and AmountSpent are both
indexed (non unique).

What I would like to do is, for all of the records in descending order
of "AmountSpen t" where "CustSelect ed = TRUE", set CustSelected to FALSE
such that the sum of all the AmountSpent records with CustSelected =
TRUE is no greater than a specified amount (say $50,000).

What I'm doing at the moment is a "SELECT * FROM CustTransaction s WHERE
CustSelected = TRUE ORDER BY AmountSpent;", programatically looping
through all the records until AmountSpent 50000, then continuine to
loop through the remainder of the records setting CustSelected = FALSE.
This does exactly what I want but is slow and inefficient. I am sure it
could be done in a single SQL statement with subqueries, but I lack the
knowledge and experience to figure out how.

The closest I can get is:-

UPDATE CustTransaction s SET CustSelected = FALSE
WHERE (CustSelected = TRUE)
AND TransactionKey NOT IN
(SELECT TOP 50000 TransactionKey FROM CustTransaction s WHERE
(((CustTransact ions.CustSelect ed)=TRUE))
ORDER BY AmountSpect DESC, TransactionKey ASC);

However, this mereley ensures only the top 50,000 customers by amount
spent remain "selected", not the top "X" customers whose total spend
is $50,000. I really need to replace the "SELECT TOP 50000" with some
form of "SELECT TOP (X rows until sum(AmountSpent ) =50000)".

Is it even possible to achieve what I'm trying to do?

Thanks in advance for any assistance offered!
--
SlowerThanYou

[Slower Than You]

--CELKO-- wrote:

No, I didn't know that SQL has no boolean data type, and that BIT

is proprietary, so thanks for that information. You can pretend it is
an integer type if you prefer. Again, do not read anything into the
table and field [sic] names .. <<

O)kay. You have SERIOUS conceptual problems with SQL and RDBMS.

Yeah well thanks for the opinion and all, but with the helpful efforts
of a number of posters to this group, I've understood and solved the
problem now and everything is just lovely. I'm happy, my customer is
happy, and my customer's customer is happy. Flowers bloom, birds sing,
and I've moved on to other things.

The last time I did any serious database development work was as a
young contractor, way back in the days of DBaseII before all this SQL
malarkey existed. In those days we had tables that consisted of records
made up of one or more fields. Rows and columns where for spreadsheets.
I'm sorry if that terminology annoys you but old habits die hard, and
it least it gives you a reason to try to act all superior, eh?
--
SlowerThanYou

[Ed Murphy]

--CELKO-- wrote:

You do not have a table at all; it is an attempt to mimic a deck of
punch cards. You confuse columns and fields, rows and records

This would be a lot more helpful if you'd explain the difference (or
rather, since the explanation is probably long-ish, include a URL
where the explanation can be found).

do these transactions create a customer or a sale?

*looks down* Oh, you're alluding to SalesTransactio ns being a better
name than CustTransaction s. But are you sure? Customers may engage
in sales, returns, credit memos and debit memos (the latter two are
used to adjust the customer's balance without inventory changing hands,
e.g. if they were over/undercharged for something). Of course, stuffing
multiple types of transactions into a single table without an explicit
TransactionType column is a separate error, but perhaps the table
definition was simplified by omitting columns not directly relevant to
the task at hand.

Why is there DDL in narratives?

Why wouldn't there be? How many questions lacking DDL receive
the initial response "please post DDL to create your tables and
populate them with data illustrating the issue"?

>>What I would like to do is, for all of the records [sic] in descending order of "AmountSpen t" where "CustSelect ed = TRUE", set CustSelected to FALSE

such that the sum of all the AmountSpent records with CustSelected =
TRUE is no greater than a specified amount (say $50,000). <<

You did not say what to do about ties; if I have five sales of
$50,000.00 which one would I mark?

This gap in the spec can be bridged by picking an arbitrary
rule (e.g. mark rows with lower TransactionKey first), on the
assumption that the questioner will be able to adjust that
part of the answer to fit whatever the actual rule is.

give us a RELATIONAL spec and we can probably help you

This is a more general case of the above. "Your style is lousy,
so I'm going to point that out _and not answer your question_."

[Ed Murphy]

--CELKO-- wrote:

O)kay. You have SERIOUS conceptual problems with SQL and RDBMS. The
reason that SQL has no BOOLEAN data types is one of those "mathematic al
foundations" things that has to do with NULLs, 3-valued logic and
logic.

This is not quite true. SQL in general has an optional BOOLEAN data
type; MSSQL in particular does not support the option.

http://troels.arvin.dk/db/rdbms/#data_types-boolean

Also, most of your message boils down to "you shouldn't store computed
data that can become outdated", but starting it out with the
above-quoted material gives the impression of "you shouldn't use
flags", which is untrue.

Fields have mean because of the program that reads them; columns have
a domain, a value and constraints in the schema -- totally separate
from any program that uses them -- which give them meaning.

Aha, here's the answer to that "what's the difference between a field
and a column?" question that was raised earlier. No wonder I felt
confused - I'm familiar with program-independent constraints enforced
by the database, but did not strictly associate "column" with their
existence and "field" with their non-existence. (Spreadsheets, in
particular, play havoc with this.)

The intended difference between "row" and "record" is similarly
non-obvious to the lay reader, though I think I've read about it
before: namely, records have an inherent order, while rows have
no guaranteed order unless you specify one. (Spreadsheets play
havoc with this, too. So do certain indexes, especially clustering
indexes, which novices can easily mistake for an inherent order.)

[--CELKO--]

See if this is what you want:

CREATE TABLE SalesTransactio ns
(sales_nbr INTEGER NOT NULL PRIMARY KEY,
customer_id INTEGER NOT NULL
REFERENCES Customers (customer_id),
sales_amt DECIMAL(12,2) NOT NULL);

Create a VIEW or CTE with each customers sales ordered from high to
low. This is a greedy algorithm. The ROW_NUMBER() will randomly pick
an ordering in the event of ties.

Using that derived table, we can find the subset of purchase in each
customer that are at or below the threshold. amount, something like
this:

WITH (SELECT customer_id, sales_amt,
ROW_NUMBER()
OVER (PARTITION BY customer_id
ORDER BY sales_amt DESC)
FROM SalesTransactio ns AS S1)
AS SalesScores (customer_id, sales_amt, score)

SELECT S1.customer_id, S1.score
FROM SalesScores AS S1
WHERE @threshold_amt <=
(SELECT SUM(S2.sales_am t)
FROM SalesScores AS S2
WHERE S1.customer_id = S2.customer_id
AND S1.score >= S2.score);

You can do this in one statement with the full OLAP features, which
would have a RANGE clause in the SUM() OVER() construct. SQL Server is
a bit behind.

But the important point is that you use virtual tables, rather than
mimicing a deck of punch cards. Think LOGICAL and not PHYSICAL! Think
sets, not sequences.