|
|
Posted by Razvan Socol on 11/18/06 07:28
Hi,
Consider the following sample data:
INSERT INTO CustTransactions VALUES (1, 1000, 0)
INSERT INTO CustTransactions VALUES (2, 1000, 1)
INSERT INTO CustTransactions VALUES (2, 2500, 1)
INSERT INTO CustTransactions VALUES (1, 1000, 1)
INSERT INTO CustTransactions VALUES (1, 1000, 1)
INSERT INTO CustTransactions VALUES (3, 30000, 1)
INSERT INTO CustTransactions VALUES (3, 17000, 1)
What is the expected result (the output of SELECT * FROM
CustTransactions) ?
Also consider this sample data:
INSERT INTO CustTransactions VALUES (1, 10000, 0)
INSERT INTO CustTransactions VALUES (2, 20000, 1)
INSERT INTO CustTransactions VALUES (2, 25000, 0)
INSERT INTO CustTransactions VALUES (2, 2500, 0)
What is the expected result in this case ?
Razvan
Slower Than You wrote:
> 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]
|