|
|
Posted by Slower Than You on 11/17/06 19:04
Well, I think it's complex anyway -- you might not :)
TableDef:
CREATE TABLE CustTransactions (
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 "AmountSpent" where "CustSelected = 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 CustTransactions 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 CustTransactions SET CustSelected = FALSE
WHERE (CustSelected = TRUE)
AND TransactionKey NOT IN
(SELECT TOP 50000 TransactionKey FROM CustTransactions WHERE
(((CustTransactions.CustSelected)=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
Navigation:
[Reply to this message]
|