Form that calculates values from different columns between date range

[redstarx]

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,

[ginerjm]

Some things wrong with your code I think

1 - you can't group a query unless you have some kind of summarization going on.  Perhaps you meant to 'order by'.

 

2 - you have the guts of your next query  - you just want to figure out how to build the where clause.

 

Why not start slow and build the input/selection form first and grab the values and validate them.  That's a big part of this process - to be sure you are able to get the values and to be sure that you either scrub them properly or build prepared queries to use them in.  Then you can start to work on the code to examine each input value and decide how to build the "where" clause.

 

Keep us posted!