DBGrid: SUM contiguous row UPTO a certain point

well .. one way to go would be using a stored proc. but then you'll be limited to using a certain number of db servers.

or, programatically:

adoquery.sql.text:='select *,qnt as agg_sum from table'
adoquery.open;
then
var total:integer;
begin
  with ADOQuery1 do
  begin
    First;
    total:=FieldByName('agg_sum').AsInteger;
    Next;
    while not Eof do
      FieldByName('agg_sum').AsInteger:=total+FieldByName('agg_sum').AsInteger;
  end;
end;

not tested, but that is the general idea.

writing the stored procedure is done in a similar (recursive) way.

i have never used ado but i think you should be able to use a calculated field. right click on the qry component, choose fieldeditor and add all fields. then in the field editor choose new field, calculated. click on the field you just created and look at the propertied. there is prob. a expression you can fill somewhere.

Thanks for your help guys but none of your approaches have worked for me. Any more suggestions ?

my solution should have worked. why do you say it didn't work for you?

You also could do a nested select to get that answer, but I don't know all your table names and where clause.

We do need one marker to let us know that each record is individual, from the records above the date and custid should work for more than one customer

SELECT Cus_Code, Transaction_Kind, Transaction_Date, Price, Qnt,
  (SELECT Sum(qty) FROM yourtable WHERE Cus_Code = y.Cus_Code and transaction_date >= y.transaction_date) AS Aggregate_Sum
FROM
  yourtable y

show your select you have (specially the sort order is from interest)

guess Mike is near

meikl ;-)

Sorry I take so long to answer back, doing several things and testing your suggestions at the same time.
Here's my SELECT:

SELECT MOV.stck_code AS 'Product Code', MOV.mov_date AS 'Date', MOV.parast AS 'Invoive Code', MOV.mov_quant AS 'Quantity', MOV.price AS 'Price', MOV.tel_axia AS 'End Price', DATEDIFF( Day, MOV.mov_date, GETDATE()) AS 'Days From Today',
(CASE
  WHEN DATEDIFF( Day, MOV.mov_date, GETDATE()) BETWEEN 0 AND 30 THEN '0 - 30'
  WHEN DATEDIFF( Day, MOV.mov_date, GETDATE()) BETWEEN 31 AND 60 THEN '31 - 60'
  WHEN DATEDIFF( Day, MOV.mov_date, GETDATE()) BETWEEN 61 AND 90 THEN '61 - 90'
  WHEN DATEDIFF( Day, MOV.mov_date, GETDATE()) BETWEEN 91 AND 120 THEN '91 - 120'
  WHEN DATEDIFF( Day, MOV.mov_date, GETDATE()) BETWEEN 121 AND 150 THEN '121 - 150'
  WHEN DATEDIFF( Day, MOV.mov_date, GETDATE()) BETWEEN 151 AND 180 THEN '151 - 180'
  WHEN DATEDIFF( Day, MOV.mov_date, GETDATE()) > 180 THEN '180 +'
END)  AS 'Aging'
FROM SQLSRV2.dbo.MOV
WHERE (MOV.stck_code LIKE :searchString) AND (MOV.parast Like 'TGK%')  AND (MOV.mov_date>=:StartDate) AND (MOV.mov_date<=:EndDate)
AND (DATEDIFF( Day, MOV.mov_date, GETDATE())>=:LowAge) AND  (DATEDIFF( Day, MOV.mov_date, GETDATE())<=:HighAge)
ORDER BY MOV.mov_date DESC, MOV.stck_code

Thanks for your replies. I'd like to report a somewhat unexplainable behaviour. Whenever the mouse scroller is used or when the DBGrid's vertical scrollbar is clicked, the AutoCalcFields get recalculated *ONLY *WHEN the number of returned rows exceeds the height of the DBGrid. A sample dataset is as follows:

Cus_Code   Transaction_Kind   Transaction_Date   Price   Qnt    Condition
00000001          TGK756                11/9/06           44.4    54     54          
00000001          TGK755                10/9/06           43.3    53     53+54
00000001          TGK754                9/9/06             42.2    52     52+53+54
00000001          TGK753                8/9/06             41.1    51     51+52+53+54

Which is correct. When however the number of returned rows is large and thus the mouse scroller is used to view them I get the following results:

Cus_Code   Transaction_Kind   Transaction_Date   Price   Qnt    Condition
00000001          TGK756                11/9/06           44.4    54     54+(51+52+53+54) <- Sum Before MouseScroller used / Before DBGrid Scrollbar Clicked
00000001          TGK755                10/9/06           43.3    53     53+54+(51+52+53+54)
00000001          TGK754                9/9/06             42.2    52     52+53+54+(51+52+53+54)
00000001          TGK753                8/9/06             41.1    51     51+52+53+54+(51+52+53+54)

Any suggestions, workarounds or possible explanations? I've used the following in the onCalcFields Event of my ADOQuery:

   PreviousSum := iPreviousSum + Search_AQ.FieldByName('Quantity').AsInteger ;
   Search_AQ.FieldByName('Condition').AsInteger := iPreviousSum ;

as wimmeyvaert suggested. Thanks again

And have you tried my suggestion in using a query instead ?
I don't know if this is possible for you (I mean if you have a unique recordID and sorting on this Id).

Pleaase let us know.

Greetz,

The Mayor.

Thank for your reply wimmeyvaert. Yes I have tried your suggestion, I have a unique Primary key (called stck_code), i used a query, used the appropriate WHERE clauses to fetch only the required piece of data, used identical ORDER BY clauses but I got no results returned. That's why I implemented the much quicker/simpler solution of onCalcFields. Can you think of any reason why Im getting this strange behaviour?

what i am about to suggest is soo wrong (redundancy), but also soo practcal.

the data you are gathering are prob. being used in a report as well at some point. Eventhough it is a calculated field i would opt for adding a table to my dbase and put the calc.  value and the prim. key of the parent in it. You can use this if and when  you build your reports as well, and a simple delete from tmptable, insert into temptable will make sure all the data is accurate