Get percentages from times

[NorthStarDA]

I have a report written to display total times worked on jobs in a time management app. Here is my query..

<cfquery datasource="#Application.DSN#" name="getprojects">
SELECT h.project_id,h.time_in,h.time_out,p.name,p.id,
SEC_TO_TIME(SUM(TIME_TO_SEC(h.time_out)-TIME_TO_SEC(h.time_in))) TOTAL_TIME
FROM work_history h,projects p
WHERE h.work_date BETWEEN #CreateODBCDate(URL.from)# AND #CreateODBCDate(URL.to)#
AND p.ID = h.project_id
GROUP BY p.ID ORDER BY p.name
</cfquery>

this works fine (thanks to the forums) and outputs data like so:
Projects Total Time
Project1 06:36:14
Project2 14:03:54
Project3 08:38:03
Project4 03:36:03

i need to build an xml doc with this data however, i cannot use times, i need to convert them to percentages like so:

Projects Total Time
Project1 26
Project2 38
Project3 31
Project4 5

I dont need the percent symbol, just the number and I would like to do this in the query. The total times that are displaying are fine because i need them for another part of the report, but I would also like to get the percentages as well for the graph.

Any ideas?

 

[r937]

what database?

rudy

http://r937.com/

SQL Consulting

 

[NorthStarDA]

MySQL 4.0 is my database

 

[r937]

ah

okay, i don't think you can do this easily in just sql, but there's a neat way to do it using a UNION query (which mysql 4 supports) and some CF logic

please read

http://r937.com/grouptotalsanddetails.html

and let me know if you have any questions adapting this to your situation

basically you want the totals subquery to get the overall time for all projects, then in the nested CFOUTPUT, calculate your percentage for each detail row based on the saved total time value

rudy

http://r937.com/

SQL Consulting

 

[NorthStarDA]

thanks much!, i'll try that out and see if it works for me