This is a migrated thread and some comments may be shown as answers.

Sum aggregate on field of type TimeSpan

5 Answers 186 Views
Grid
This is a migrated thread and some comments may be shown as answers.
andieje
Top achievements
Rank 1
andieje asked on 29 Jun 2009, 10:55 AM
Hi

Is it possible to perform a SUM aggregate on a field of type TimeSpan? When I try it I get an error: 'Invalid usage of aggregate function SUM and type: Timespan'

thanks

5 Answers, 1 is accepted

Sort by
0
andieje
Top achievements
Rank 1
answered on 01 Jul 2009, 08:06 PM
Hi

I would really appreciate it if someone from tech support could look at this question as I notice other questions posted after this one have already been answered.

Many thanks in advance
0
Princy
Top achievements
Rank 1
answered on 02 Jul 2009, 10:18 AM
Hi,

A  suggestion would be to store the Timespan in seconds with type as integer  in the database. This way you will be able to calculate the total number of seconds using the Aggregate. Then you can convert the value to datetime and then format it  to display in hours and minutes using the following code :

new DateTime(timeSpan.Ticks).ToString("HH:mm:ss")


Thanks,
Princy


0
Thuc Nguyen
Top achievements
Rank 1
answered on 14 Jul 2009, 04:39 AM
I'm having the same problem with Andieje. This is the greatest answer if someone give a solutions.

To Princy: your solution does not work on my case, i'm using ASP.Net tags to explain the Aggregate

<telerik:GridGroupByField FieldName="SpentTime" HeaderText="Total spent time" Aggregate="Sum" />

Thanks,
Thuc
0
andieje
Top achievements
Rank 1
answered on 14 Jul 2009, 03:07 PM
Dear Thuc

There is no 'built in' answer to the problem that I am aware of. There are 2 ways around it that i know of

1) As Princy said, create an extra field in your database that has type Integer and stores the time in seconds. You can have this as a hidden column in your grid, let the radgrid calculate the aggregate for you and then display this calculation in the footer of another field

<telerik:GridBoundColumn HeaderText="Seconds" Visible="false" DataField="TimeInSecondsFromLastPoint" Aggregate="SUM" FooterAggregateFormatString="{0}"></telerik:GridBoundColumn> 
 <telerik:GridBoundColumn HeaderText="Time From Last Point" DataField="TimeFromLastPoint">  
</telerik:GridBoundColumn> 
 in item data bound  
 
If TypeOf e.Item Is GridFooterItem Then  
Dim ts As New TimeSpan(0, 0, Integer.Parse(footerItem("TimeInSecondsFromLastPoint").Text))  
 
footerItem("TimeFromLastPoint").Text = String.Format("Total time: {0: hh:mm:ss}", ts)  
 

If you can't change the database naturally this solution won't be any use and you will have to go with option 2 i think.
 
2)calculate the aggregate manually in the item databound event by keeping a running total of the column.

Hope this helps
andrea
0
Fernando
Top achievements
Rank 1
answered on 03 Apr 2014, 03:57 PM
Hi, I'm using MVC(CSHTML) how can aply this solution for a razor envoriment ?

Thanks!
Tags
Grid
Asked by
andieje
Top achievements
Rank 1
Answers by
andieje
Top achievements
Rank 1
Princy
Top achievements
Rank 1
Thuc Nguyen
Top achievements
Rank 1
Fernando
Top achievements
Rank 1
Share this question
or