Hello,
I've been going about reading different posts on here and other forums but so far I haven't been able to come across something that works for my purpose so after all the reading I thought I'd finally just ask. I'm rather new at php/sql queries so please bare with me.
First what I'm trying to accomplish.
I need a form with several fields (options) to fetch information from a table that will then display the results based on the options selected.
- from date
- to date
- employee name
- department
- branch
- product line
In other words, the purpose is to be able to choose a date range (from one day to up to a year or more) and then to be able to choose either ONE employee name to view statistics invididually from a given department and branch or to select a department to view all the statistics for everyone under that given department and/or brach and/or product line.
Now the tricky part is that besides fetching the records, calculations need to be made before the records are displayed and that part right here is what is giving me a huge headache.
This is the query that I have.
SELECT
report_daily_id,
report_date,
emp_id,
emp_fullname,
emp_dept,
emp_branch
prod_line
calls,
tk_time,
hld_time,
ac_time,
tran_calls,
work_time,
tran_rate,
ah_time
FROM daily_report
GROUP BY emp_id, emp_fullname, emp_branch, emp_dept, prod_line
The calculations based on the date range and one or more of the other options need to give me the following results.
SUM(calls) AS 'Total Calls'
SUM(tk_time) / SUM(calls) AS 'Talk Time'
SUM(hld_time) / SUM(calls) AS 'Held Time'
SUM(ac_time) / SUM(calls) AS 'AC Time'
SUM(tran_calls) AS 'Total Trans'
SUM(tran_calls) / SUM(calls) AS 'Tran Rate'
SUM(ah_time) / SUM(calls) AS 'AH Time'
I don't know how else to explain myself past this point but if you have any questions, perhaps I can answer it and give more details.
Thank you,
