Phreonx
asked on
DBGrid, ADOQuery, MSSQL: Row Sum Revisited
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
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
Forgot to say the table C would be your outside table (should be the same one as in the nested select).
For example
SELECT ID, Cus_Code, Transaction_Kind, Transaction_Date, Price, Qnt,
( SELECT SUM( Qnt ) FROM yourtable WHERE Cus_Code = C.Cus_Code AND ID >= C.ID ) AS Aggregate_SUM
FROM
yourtable C
WHERE
..................
For example
SELECT ID, Cus_Code, Transaction_Kind, Transaction_Date, Price, Qnt,
( SELECT SUM( Qnt ) FROM yourtable WHERE Cus_Code = C.Cus_Code AND ID >= C.ID ) AS Aggregate_SUM
FROM
yourtable C
WHERE
..................
If you wanted to go back to something along the lines of your other question
https://www.experts-exchange.com/questions/21985877/DBGrid-SUM-contiguous-row-UPTO-a-certain-point.html
You could again hold a local variable and just reset it on change of Cus_Code and start to sum the quantity field again.
I prefer doing it in a query and letting the server do the work, just make sure you have your main WHERE clause fields indexed if you can.
https://www.experts-exchange.com/questions/21985877/DBGrid-SUM-contiguous-row-UPTO-a-certain-point.html
You could again hold a local variable and just reset it on change of Cus_Code and start to sum the quantity field again.
I prefer doing it in a query and letting the server do the work, just make sure you have your main WHERE clause fields indexed if you can.
ASKER
Mike:
"If you wanted to go back to something along the lines of your other question"
yes that is exactly what Im looking for. However, the requirements changed dramatically since yesterday and Im recoding a major part of my initial code. That's why the calculation formula of the Aggregate_Sum Field changed.
"You could again hold a local variable and just reset it on change of Cus_Code and start to sum the quantity field again."
I tried implementing this solution this morning but unfortunately got caught up in several other activities and could not finish it.
I'll first try a nested select as you suggested otherwise Im going to need your help regarding the second approach.
Thanks
"If you wanted to go back to something along the lines of your other question"
yes that is exactly what Im looking for. However, the requirements changed dramatically since yesterday and Im recoding a major part of my initial code. That's why the calculation formula of the Aggregate_Sum Field changed.
"You could again hold a local variable and just reset it on change of Cus_Code and start to sum the quantity field again."
I tried implementing this solution this morning but unfortunately got caught up in several other activities and could not finish it.
I'll first try a nested select as you suggested otherwise Im going to need your help regarding the second approach.
Thanks
ASKER
No luck with the nested SELECTs
How does your main query behind
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
know what order it is to be displayed
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
know what order it is to be displayed
ASKER
If you mean the ORDER BY clause, it is ORDER BY Cus_Code ASC
Well the first issue I can see is how does it know which of these 2 would be displayed first
00000004 TGK755 10/9/06 43.3 50
00000004 TGK755 10/9/06 43.3 30
If the SQL just chooses at random, then it is impossible to really work out in which order to SUM the quantities.
A Nested select should do the job if it has the correct order.
Is the query the same as the one you posted on the last question?
00000004 TGK755 10/9/06 43.3 50
00000004 TGK755 10/9/06 43.3 30
If the SQL just chooses at random, then it is impossible to really work out in which order to SUM the quantities.
A Nested select should do the job if it has the correct order.
Is the query the same as the one you posted on the last question?
ASKER
"Well the first issue I can see is how does it know which of these 2 would be displayed first"
Well, I don't see any apparent way to distinguish the order between the two and this is due to the way the database table was initially created.
"Is the query the same as the one you posted on the last question?"
No. If we manage though, using the query from my previous question, to have the required data returned, I can revert with no great hassle
Well, I don't see any apparent way to distinguish the order between the two and this is due to the way the database table was initially created.
"Is the query the same as the one you posted on the last question?"
No. If we manage though, using the query from my previous question, to have the required data returned, I can revert with no great hassle
ASKER
Ok, there's a field I could use in order to discriminate between these two. It's called aa:
AA Cus_Code Transaction_Kind Transaction_Date Price Qnt Aggregate_SUM
99 00000004 TGK755 10/9/06 43.3 50
98 00000004 TGK755 10/9/06 43.3 30
Hope that helps
AA Cus_Code Transaction_Kind Transaction_Date Price Qnt Aggregate_SUM
99 00000004 TGK755 10/9/06 43.3 50
98 00000004 TGK755 10/9/06 43.3 30
Hope that helps
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
oops just noticed you want the sum at the bottom
just need to change the date check around
select Cus_Code, Transaction_Kind, Transaction_Date, Price, Quantity,
(select sum(Quantity) from miketemp where Cus_Code = C.Cus_Code and Transaction_Date >= C.Transaction_Date)
from miketemp C
order by Cus_Code, Transaction_Date DESC
Just play around with the order of Transaction_Date and the Transaction_Date >= C.Transaction_Date until you get the correct order you are looking for (obvioously only 4 options)
just need to change the date check around
select Cus_Code, Transaction_Kind, Transaction_Date, Price, Quantity,
(select sum(Quantity) from miketemp where Cus_Code = C.Cus_Code and Transaction_Date >= C.Transaction_Date)
from miketemp C
order by Cus_Code, Transaction_Date DESC
Just play around with the order of Transaction_Date and the Transaction_Date >= C.Transaction_Date until you get the correct order you are looking for (obvioously only 4 options)
If you can create a local table, you can manuplate the data and pump the data into the local table and show this to the grid.
>>I would like however, to include a dynamic field to SUM the Qnt but in the following fashion:
Fetch data from server order by for Cus_Code
Then create a temp table.
Check the Cust_Code changes or not through delphi,
if not insert the record into temp table. Hold the required value in a variable(aggregare sum)
go to next record, check for the Cust_Code changes or not. If not changed add the required value into the variable and insert that record into the temp table.
Follow until the end of the records.
Connect the temp table to the grid.
This can be done only if the you are allowed to create and pump data into the local table. All depends on your situations.
sun4sunday
>>I would like however, to include a dynamic field to SUM the Qnt but in the following fashion:
Fetch data from server order by for Cus_Code
Then create a temp table.
Check the Cust_Code changes or not through delphi,
if not insert the record into temp table. Hold the required value in a variable(aggregare sum)
go to next record, check for the Cust_Code changes or not. If not changed add the required value into the variable and insert that record into the temp table.
Follow until the end of the records.
Connect the temp table to the grid.
This can be done only if the you are allowed to create and pump data into the local table. All depends on your situations.
sun4sunday
ASKER
Mike, I tried your solution on Northwind after I modified the original table to include some redundancy and I'm indeed getting the required dataset returned. I'll get back to you tomorrow and hopefully, after the required adaptations, your solution will do the trick. Thanks for your time
ASKER
Works great Mike. Is there a way to filter the results first, based on Transaction_Kind (eg Where Transaction_Kind LIKE LGA%) and then apply the calculation to get the same results ?
You should be able to put in the WHERE clause
Using the last set of data I put in
where Transaction_Kind LIKE 'TGK755%'
and I got these results
00000001 TGK755 2006-09-10 10:00:00.000 43.30 60 110
00000001 TGK755 2006-09-10 00:00:00.000 43.30 30 140
00000003 TGK755 2006-09-10 00:00:00.000 43.30 80 80
00000004 TGK755 2006-09-10 10:00:00.000 43.30 50 50
00000004 TGK755 2006-09-10 00:00:00.000 43.30 30 80
Using the last set of data I put in
where Transaction_Kind LIKE 'TGK755%'
and I got these results
00000001 TGK755 2006-09-10 10:00:00.000 43.30 60 110
00000001 TGK755 2006-09-10 00:00:00.000 43.30 30 140
00000003 TGK755 2006-09-10 00:00:00.000 43.30 80 80
00000004 TGK755 2006-09-10 10:00:00.000 43.30 50 50
00000004 TGK755 2006-09-10 00:00:00.000 43.30 30 80
For example
00000004 TGK755 10/9/06 43.3 50 50
00000004 TGK755 10/9/06 43.3 30 50+30
Now if there was a unique ID per row (which really you should have),
Say it looked like this
ID Cus_Code Transaction_Kind Transaction_Date Price Qnt Aggregate_SUM
1 00000004 TGK755 10/9/06 43.3 50 50
2 00000004 TGK755 10/9/06 43.3 30 50+30
You would be able to do a nested select that would look something like
( SELECT SUM( Qnt ) FROM yourtable WHERE Cus_Code = C.Cus_Code AND ID >= C.ID ) AS Aggregate_SUM