Sum of time

[skordis]

I am using a Datediff in my query to get the time spent in certain places. I want to be able to insert a sum of "Time Spent" for all the users in my report footer.

When I click on summary, I don't get the option to choose Sum. It is either Max, Min, Count.....

Can anyone help?

Thanks

 

[synapsevampire]

Try posting what you used, the version of your software/daatbase, example data and expected results.

A datediff in a query sounds like you're usign a SQL Expression, or an add command and custom SQL, or you are not using proper technical terms.

But we need to know details.

For instance what is the format of this time that you are summing? Don't assume that every database has every tiem stored the same, post what you have and what you need.

I have a FAQ on displaying times, which uses a datediff to compute the seconds difference between 2 datetimes and then display it in HH:MM:SS format here:

faq767-3543

-k

 

[skordis]

This is my query :

SELECT user_info.username, user_info.first_name + ' ' + user_info.last_name AS [user_name],
skordis.FINDHOURS (Sum(DateDiff("s",user_tracking.time_in,user_tracking.time_out))) AS [Time Spent in Phase],
form.form_id, form.name, form.phase_id, phase.name,
FROM ((form
INNER JOIN user_tracking
ON form.form_id = user_tracking.form_id)
INNER JOIN user_info
ON user_tracking.user_id = user_info.user_id)
INNER JOIN phase
ON form.phase_id = phase.phase_id
WHERE (((user_tracking.time_in) Between @date1 And @date2))
GROUP BY user_info.username, user_info.first_name, user_info.last_name, form.form_id, form.name, form.phase_id, phase.name, user_info.active
HAVING (((user_info.active)=1));

I am Using Crystal Reports XI. The query is a stored procedure on an sql server. The function included in the procedure is to return hh:mm:ss.
I tried an alteration of of the query, without the function so it returns only seconds. When I return seconds only I am able to create a subtotal for all the times spent in a specific phase.

When I try to format the seconds into hh:mm:ss within CR, all I get is 00:00:00.

 

[synapsevampire]

Did you use my FAQ to display the seconds, or???

The FAQ works, so you need to post what you are trying, saying that you did something doesn't help us understand what you did, post what you are trying currently (not what you used to try, or a failing function), and show example data and the expected result.

-k

 

[skordis]

Sorry about the misuderstanding......This is my first post.
Yes I spent the whole day yesterday reading most of the FAQ and I did try your solution.

Here are the results that I received:

User1 2:12:13 PM (Time in)
2:48:27 PM (Time Out)
DateDiff = 0:36:14

User1 3:10:30 PM (Time in)
3:39:39 PM (Time Out)
DateDiff = 0:29:09

Total which should be 1:05:23 is actually the Max.

I am unable to choose Sum from the Summary

 

[lbass]

You have to do the calculation in seconds and then convert to the hh:mm:ss display. If you can't do it in the procedure, then convert the current field, something like:

(val(left({table.string},2)) * 3600) + (val(mid({table.string},4,2))* 60) + (val(mid({table.string},7,2))

Then insert summaries on the formula or use in calculations. Then as a last step, use SV's formula to convert the seconds back to a string.

-LB

 

[skordis]

Awesome, it worked.

I was trying to do so in a Cross-Tab report. I changed my report format to a regular one and I was able to insert the field to convert the seconds to hh:mm:ss

Thanks guys