troubleshooting Question

DBGrid, ADOQuery, MSSQL: Row Sum Revisited

Avatar of Phreonx
Phreonx asked on
Delphi
16 Comments1 Solution991 ViewsLast Modified:
Hello all,
I'm fetching a set of a data from an SQL Server database into a DBGrid using, an ADOConnection, an ADOQuery and a plain Datasource. Eight sample rows in the DBGrid look like this:

Cus_Code   Transaction_Kind   Transaction_Date   Price   Qnt
00000001          TGK756                11/9/06           44.4    50
00000001          TGK755                10/9/06           43.3    60
00000001          TGK755                10/9/06           43.3    30
00000002          TGK754                9/9/06             42.2    40
00000002          TGK753                8/9/06             41.1    20
00000003          TGK755                10/9/06           43.3    80
00000004          TGK755                10/9/06           43.3    50
00000004          TGK755                10/9/06           43.3    30

I have implemented a solution that right now gives me the following (expected) results:

Cus_Code   Transaction_Kind   Transaction_Date   Price   Qnt   Aggregate_SUM
00000001          TGK756                11/9/06           44.4    50    140 (30+50+60)
00000001          TGK755                10/9/06           43.3    60    140 (30+50+60)
00000001          TGK755                10/9/06           43.3    30    140 (50+60+30)
00000002          TGK754                9/9/06             42.2    40    200 (140+40+20) 140 is the previous sum
00000002          TGK753                8/9/06             41.1    20    200 (140+40+20)
00000003          TGK755                10/9/06           43.3    80     280 (200+80)
00000004          TGK755                10/9/06           43.3    50     360 (280+50+30)
00000004          TGK755                10/9/06           43.3    30     360 (280+50+30)

I would like however, to include a dynamic field to SUM the Qnt but in the following fashion:

Cus_Code   Transaction_Kind   Transaction_Date   Price   Qnt   Aggregate_SUM
00000001          TGK756                11/9/06           44.4    50    50
00000001          TGK755                10/9/06           43.3    60    50+60
00000001          TGK755                10/9/06           43.3    30    50+60+30
00000002          TGK754                9/9/06             42.2    40    40
00000002          TGK753                8/9/06             41.1    20    40+20
00000003          TGK755                10/9/06           43.3    80     80
00000004          TGK755                10/9/06           43.3    50     50
00000004          TGK755                10/9/06           43.3    30     50+30

And so on for all the rows returned by the ADOQuery. Please notice that the Aggregate_SUM field must represent the SUM of the rows with RowNumber <= CurrentRowNumber *FOR *EACH Cus_Code as shown in the above diagram. All help is welcome.

Thank you in advance
ASKER CERTIFIED SOLUTION
Mike Littlewood
Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 16 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 16 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros