MySQL/PHP to generate values by date range

Question

I've been reading plenty about crosstab reports (Pivot Tables) in PHP and I've been trying to complete a report but I'm stuck.

I have a DB Table with report_date, employee_name, employee_id, leader_name, leader_id, employee_dept, stat1, stat2, stat3, stat4, stat5, stat6.

what I'm trying to do is to be able to query and return a sum and division of values based on the report_date range selected. So if I choose to query data between 10/01/2014 & 10/25/2014, I need it to sum and divide all the values found within that range.

This is my current SQL query.

Select
report_date,
employee_name,
employee_id,
leader_name,
leader_id,
employee_dept,
stat1,
stat2,
stat3,
stat4,
stat5,
stat6,
SUM(stat2)/SUM(stat1) AS `Result Name2`,
SUM(stat3)/SUM(stat1) AS `Result Name3`,
SUM(stat4)/SUM(stat1) AS `Result Name4`,
SUM(stat5)/SUM(stat1) AS `Result Name5`,
SUM(stat6)/SUM(stat1) AS `Result Name6`
FROM daily_records
GROUP BY report_date, employee_id

Might be too much to ask but how do I use this in PHP to query the totals from a selected report_date range?

Edited to add Sample data:

DB table data

report_date employee_id employee_name   employee_dept   STAT1   STAT2   STAT3   STAT4   STAT5   STAT6   leader_name leader_id
9/11/2014   1983122     emp_name1   ARK     17  7941    191 5   8137    2   Name        1001
9/11/2014   1983130     emp_name2   ARK     11  5067    516 3   5586    1   Name        1001
9/11/2014   1983138     emp_name3   ARK     3   184 16  4   204 1   Name        1001
9/11/2014   1983138     emp_name4   ARK     12  2576    7   6   2589    2   Name        1002
9/11/2014   1983138     emp_name5   ARK     21  9069    400 139 9608    1   Name        1002
9/11/2014   1983328     emp_name6   ARK     69  17929   1893    1096    20918   1   Name        1002
9/11/2014   1983349     emp_name7   ARK     12  2259    17  112 2388    2   Name        1002
9/11/2014   1983349     emp_name8   ARK     23  8194    880 211 9285    2   Name        1003
9/11/2014   1983829     emp_name9   ARK     81  16175   1431    311 17917   2   Name        1003
9/11/2014   1983888     emp_name10  ARK     7   1442    22  9   1473    1   Name        1003
9/12/2014   1983122     emp_name1   ARK     35  6823    774 22  7619    1   Name        1001
9/12/2014   1983642     emp_name2   ARK     80  18268   1439    135 19842   2   Name        1001
9/12/2014   1983643     emp_name3   ARK     55  20321   962 466 21749   1   Name        1001
9/12/2014   1983677     emp_name4   ARK     72  16379   1157    418 17954   2   Name        1002
9/12/2014   1983682     emp_name5   ARK     17  5017    419 425 5861    1   Name        1002
9/12/2014   1983978     emp_name6   ARK     48  9898    228 94  10220   1   Name        1002

(I tried to paste the table with a proper format but I couldn't)

Stats 2 to 6 should be divided by stat 1 to get a result for each field. for example: stat2 / stat1 AS Result Name

Let me know if you need anything else.

Thanks in advance

Please provide some sample data and the exact outcome you're looking for based on the sample data — peterm
– peterm, Commented Oct 26, 2014 at 20:24
I added some sample data from the SQL table. 2 days worth of data. I'm sorry I couldn't format the table any better. — BlueSun3k1
– BlueSun3k1, Commented Oct 26, 2014 at 20:57

musa · Accepted Answer · 2014-10-26 21:02:09Z

1

First you should use DATE type for date. After append your query date condition like that;

Select
report_date,
employee_name,
employee_id,
leader_name,
leader_id,
employee_dept,
stat1,
stat2,
stat3,
stat4,
stat5,
stat6,
SUM(stat2)/SUM(stat1) AS `Result Name2`,
SUM(stat3)/SUM(stat1) AS `Result Name3`,
SUM(stat4)/SUM(stat1) AS `Result Name4`,
SUM(stat5)/SUM(stat1) AS `Result Name5`,
SUM(stat6)/SUM(stat1) AS `Result Name6`
FROM daily_records
WHERE report_date >= "2014-10-01"
OR report_date <= "2014-10-25"
GROUP BY employee_id`

answered Oct 26, 2014 at 21:02

musa

1,46518 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL/PHP to generate values by date range

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related