Is there a way to sum data in DataTables?

[Arg81]

Before I start, I have mentioned this before, but I didn't really get much help, no offense to those who did reply, but it wasn't what I was wanting to achieve.

I have a decimal field in one table that is used to represent the time spent on a request in hours, i.e. 0.25, 0.5, 0.75, 1, 1.25 etc etc.

I am trying to do a sum. Basically the relationship is linked on two fields to the master table.
The first sum needs to be the sum of all the time spend on Field1. The second sum needs to be the sum of the time spent only on Field2 for each Field1.
I.E.

Request No | Revision No | Time Spent | EmployeeID

1 -------------- 1 ---------- 0.5 --------- 16
1 -------------- 1 ---------- 0.75 -------- 5
1 -------------- 2 ---------- 0.25 -------- 12
1 -------------- 2 ---------- 0.5 --------- 2

So basically, the sum of the Time Spent on Request No 1 would be 2 hours, the sum for Request 1 Revision 1 would be 1.25 hours, and Request 1 Revision 2 would be 0.75 hours.


I can get this working by creating SQL and linking into the database, but that then relies on the user having to submit everytime they add a row to the Time table. I would like this to be real time if possible, so in a sense summing the data straight out of the DataTables.

Thanks for any help.
Luke

 

[jmcilhinney]

There is no magic bullet that will do this for you. You just need to iterate over all the rows and add up the values. You might like to use nested collections to store the running totals. I would suggest a master SortedList, where the key is the Request No and the value is an ArrayList. Each ArrayList would contain two items, the first of which would be the total time spent on that request. The second item would be another SortedList, where the key is the Revision No and the value is the total time soent on that revision:

		Dim requestTimes As New SortedList
		Dim requestProperties As ArrayList
		Dim revisionTimes As SortedList

		For Each row As DataRow In myTable.Rows
			If requestTimes.ContainsKey(row("RequestNo")) Then
			    'Get the existing entry for this request.
			    requestProperties = DirectCast(requestTimes(row("RequestNo")), ArrayList)
			Else
				'Add a new entry for this request.
			    requestProperties = New ArrayList(New Object() {0D, New SortedList})
			    requestTimes.Add(row("RequestNo"), requestProperties)
			End If

			'Add the time spent to the running total for the request.
		    requestProperties(0) = CDec(requestProperties(0)) + CDec(row("TimeSpent"))

			'Get the list of times for each revision for the current request.
			revisionTimes = DirectCast(requestProperties(1), SortedList)

			If revisionTimes.ContainsKey(row("RevisionNo")) Then
			    'Add the time spent to the running total for the revision.
			    revisionTimes(row("RevisionNo")) = CDec(revisionTimes(row("RevisionNo"))) + CDec(row("TimeSpent"))
			Else
				'Add a new entry for this revision.
			    revisionTimes.Add(row("RevisionNo"), CDec(row("TimeSpent")))
			End If
		Next row

You would then access the total time for request 1 like this:

Dim time As Decimal = CDec(DirectCast(requestTimes(1), ArrayList)(0))

and the totla time for request 1, revision 1 like this:

Dim time As Decimal = CDec(DirectCast(DirectCast(requestTimes(1), ArrayList)(1), SortedList)(1))

 

[Arg81]

Hi JM,

I think I understand what you are saying. However, do I put the SortedList and ArrayList code after my parameter query has run?

However, you have then put how to access the times for a "given" request, I need it for whatever request - my parameter query loads on @RequestNumber.

I don't think there's an easy way around this, I may just remove the labels from the main form and add them to another, and go back to the way I was doing it before. At least that way the data is refreshed when the new form is opened.

Thanks for the help.
Luke

 

[jmcilhinney]

You would have to execute this code after your query because it is using the data that the query returns. This code allows for an unlimited number of requests and revisions. If your query is only returning data for a single request at a time then it is even simpler because you would not need to use the outer SortedList.

 

[Arg81]

I see.

One form brings back one record at a time, but then I also have other search forms which will bring back more than one record.

I'll have a play about with it later, see if I can get it working!

Thanks for the help.
Luke

 

[Arg81]

Hey JM,

I've set the code and everything seems to be OK.

However, how would I bind a label to each of the lists to show the time spent?

cheers,
Luke

 

[jmcilhinney]

The Label control has a DataBindings property, but I've never used it. Unless the value in the Label is going to change based on something else, like a selection in a ComboBox, there is no point using data binding anyway. In that case you may as well simply set the value once by assigning to the Text property and that's that.

 

[Arg81]

Sorry that's what I meant.

From the ;

You would then access the total time for request 1 like this:
Code:

Dim time As Decimal = CDec(DirectCast(requestTimes(1), ArrayList)(0))​

and the totla time for request 1, revision 1 like this:
Code:

Dim time As Decimal = CDec(DirectCast(DirectCast(requestTimes(1), ArrayList)(1), SortedList)(1))​

How would I display these values in labels?

Thanks
Luke

 

[jmcilhinney]

You would simply call ToString on those values and assign them to the Text property of a Label, e.g. To display the time for Request 3, Revision 5:

myLabel.Text = DirectCast(DirectCast(requestTimes(3), ArrayList)(1), SortedList)(5).ToString()

Note that those are keys you are passing to the SortedLists and not indexes. They are the actual request number and revision number, not the positions in the lists.

 

[Arg81]

I see. However, if I'm passing the keys manually, what happens when I choose another revision in the dataGrid?

the labels are on the main form and I want them to update as each revision is browsed through.

Thanks,
Luke

 

[jmcilhinney]

Just change the value of the Label's Text when the grid choice changes. If the Label is on a different form then you need a reference to that form to be able to either set the Text directly or call a method that will set it.

 

[Arg81]

Yep the label is on the same form as the datagrid.

Sorry, this part of programming is still new to me....
I have a label bound to the revision number in the datagrid, and another to the current RequestNo.

I assume I couldn't do something like;

myLabel.Text = DirectCast(DirectCast(requestTimes(lblRequestNo.text), ArrayList)(1), SortedList)(lblRevisionNumber.text).ToString()

My datagrid displays sorts the data on RevisionNumber DESC, so the highest value is at the top.
If the above isn't possible, how are you saying to change the value of the label's text when the grid row changes?

Thanks
Luke​

 

[jmcilhinney]

That line of code is basically correct, although you should convert the Text of each of those Labels to an Integer, because that is what was used as keys in the HashTables:

myLabel.Text = DirectCast(DirectCast(requestTimes(CInt(lblRequestNo.Text)), ArrayList)(1), SortedList)(CInt(lblRevisionNumber.Text)).ToString()

 

[Arg81]

Sorry to bother you again JM, but I'm getting:

An unhandled exception of type 'System.InvalidCastException' occurred in microsoft.visualbasic.dll
Additional information: Cast from string "" to type 'Integer' is not valid.

I've set the label cast as you suggested.

I've just started to get my head round this array/sort list but come crashing down when theres a problem - I don't know where to start looking!!!!

cheers,
Luke

 

[jmcilhinney]

One of your Labels must be blank, so when CInt is called to convert it to an Integer you get the exception. You need to add code to check for situations like this and handle them gracefully, e.g.

If lblRequestNo.Text <> String.Empty AndAlso lblRevisionNo.Text <> String.Empty Then	
	myLabel.Text = DirectCast(DirectCast(requestTimes(CInt(lblRequestNo.Text)), ArrayList)(1), SortedList)(CInt(lblRevisionNumber.Text)).ToString()
End If

Also, this assumes that if the Labels are not blank that they will contain numbers. If they might contain non-numerical values then you would need to accout for that too.