Top 80% query problem
- From: "jim" <anonymjim@xxxxxxxxx>
- Date: 28 Sep 2006 09:26:30 -0700
I'm trying to get a list of customers who have the top 80% of sales.
I'm using a TADOQuery in Delphi 2006 to attempt to get the results.
What I'm trying to do is get a total of sales by walking the query
results once. Then I'm finding 80% of total sales and moving the
dataset back to the first record. I'm then walking the dataset,
counting down the top 80% total. Each record in the top 80% is getting
edited and posted. I'm doing this to add some additional information
to the query. After the top 80% of records is done, I'm deleting the
rest of the rows.
The problem I'm having is that I'm somehow ending up deleting all rows
in the dataset. The dataset should be ordered by sales. Am I hitting
the top 80% rows a second time because I'm updating them? Please help.
Thanks in advance.
Here is the code. I've simplified a bit to make it easier to read.
// visual control on a form
TADOQuery:qCUSSLS;
with qCUSSLS do
begin
DisableControls;
Close;
Prepared := False;
SQL.Clear;
// put into temporary table so additional columns can be added to
table
qry := 'select * into #temp from D_CUSSLS where RecordStatus =
''A''';
qry := qry + ';alter table #temp add CYTDBudget numeric NULL;'
qry := qry + 'select * from #temp';
qry := qry + ' order by CYSales DESC';
SQL.Add(qry);
Prepared := True;
Open;
First;
// accumulate total sales
Top80Tot := 0;
while not Eof do
begin
Top80Tot := Top80Tot + qCUSSLSCYSales.Value;
Next;
end;
First;
// get 80% of total sales
Top80Tot := Top80Tot * 0.8;
while not Eof do
begin
delflg := False;
if Top80Tot < 0 then
begin
// delete row if it's not in the top 80%
delflg := True;
Delete;
end else
Top80Tot := Top80Tot - qCUSSLSCYSales.Value;
if not delflg then
begin
Edit;
qCUSSLSCYTDBudget.Value := 'logic omitted';
end;
if not delflg then
begin
Post;
Next;
end;
end;
First;
end;
.
- Follow-Ups:
- Re: Top 80% query problem
- From: Vitali Kalinin
- Re: Top 80% query problem
- Prev by Date: Re: How to insert/download files using BLOB field.
- Next by Date: Re: Top 80% query problem
- Previous by thread: RecordCount and Filtered
- Next by thread: Re: Top 80% query problem
- Index(es):
Relevant Pages
|
