Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sajitha
Starting Member
10 Posts |
 Posted - 2012-08-07 : 06:10:18
|
| I want to display total hours, that is in hh:mm:ss format. I used two formulas.One for converting time to seconds and this will return sum in seconds and another function for converting back to time. Function 1local stringvar array completetime;local numbervar totalseconds;completetime:=split({vwAttendance.Hours},":");totalseconds:= (3600*cdbl(completetime[1])) + (60*cdbl(completetime[2]))+(cdbl(completetime[3]));Function 2replace(cstr(Sum ({@tot_seconds})/3600),".00","") + ":" + replace(cstr((Sum ({@tot_seconds}) mod 3600)/60),".00","")+ ":" + replace(cstr(Sum ({@tot_seconds}) mod 60),".00","")I copied this function from internet. Its working but there is some small variation in result. For example,in my application I put two timings like 00:50:00 and 10:00:00, then the result came as 10:83:50.The first function working well, but the second function was not working properly, because its showed braket missing problem near floor. So I took the floor off from the below function.Then its worked, but showing incorrect total hour.replace(cstr(floor(Sum ({@tot_seconds})/3600)),".00","")+ ":" + replace(cstr(floor((Sum ({@tot_seconds}) mod 3600)/60)),".00","")+ ":" + replace(cstr(floor(Sum ({@tot_seconds}) mod 60)),".00","")Any help will be appreciated.Thank You in advance |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-08-07 : 07:08:57
|
| I don't know the nuances of Crystal Reports, but my general observation is that you DO need the FLOOR function. If you don't have the floor function, it will round - which means it will overestimate the number of hours or minutes in some cases.From my visual inspection, the brackets seem to be properly paired. I don't have Crystal Reports, so I am unable to help further, but you will need to investigate the error message about the missing brackets and fix it for this to work correctly. |
 |
|
|
|
|
|
|
|
Calculating sum of total hours in crystal report