Posted by Slower Than You on 11/21/06 03:44

Chris Cheney wrote:

> "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_337386
> 1_2
>
> HTH

Ahah! That helped enormously - thanks, much appreciated.
--
SlowerThanYou

[Back to original message]