|
|
Posted by Chris Cheney on 11/19/06 14:22
"Slower Than You" <no.way@jose> wrote in
news:1163790273.8354.0@iris.uk.clara.net:
> 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?
See example 4 (cumulative sum) of
http://www.databasejournal.com/features/mssql/article.php/10894_3373861_2
HTH
--
For e-mail address, remove the XXs
Navigation:
[Reply to this message]
|