Randy Johnson
asked on
Outputting timesheet totals
Here is the table:
tracking_id, project_id, project_time, createdat
1,1,2.5,2014-03-10
2,1,3.5,2014-03-11
3,1,4.5,2014-03-12
4,1,5.5,2014-03-13
5,1,2.5,2014-03-14
6,2,3.5,2014-03-10
7,2,3.5,2014-03-11
8,2,3.5,2014-03-12
9,2,4.5,2014-03-13
10,2,4.5,2014-03-14
Title Line of Report
Totals for 8/10 - 8/14
Project ID | Mon | Tue | Wed | Thu | Fri
Where the day columns are the total from the database for that day for the project
1. Some days might be missing.
2. the date range is dynamic.
Here is what I have so far:
http://sqlfiddle.com/#!2/69e8f0/3
The query returns the results but not in a way I can easily generate an html table.
What I need to know: is their an easy way to rewrite the query so that there is 1 row per project_id and all the totals returned...
or how would I go about writing the PHP to output 1 row per project_id and the totals for the day columns.
Attached is a sample report, but it does not correlate with the data in the fiddle.
sample-report.PNG
tracking_id, project_id, project_time, createdat
1,1,2.5,2014-03-10
2,1,3.5,2014-03-11
3,1,4.5,2014-03-12
4,1,5.5,2014-03-13
5,1,2.5,2014-03-14
6,2,3.5,2014-03-10
7,2,3.5,2014-03-11
8,2,3.5,2014-03-12
9,2,4.5,2014-03-13
10,2,4.5,2014-03-14
Title Line of Report
Totals for 8/10 - 8/14
Project ID | Mon | Tue | Wed | Thu | Fri
Where the day columns are the total from the database for that day for the project
1. Some days might be missing.
2. the date range is dynamic.
Here is what I have so far:
http://sqlfiddle.com/#!2/69e8f0/3
The query returns the results but not in a way I can easily generate an html table.
What I need to know: is their an easy way to rewrite the query so that there is 1 row per project_id and all the totals returned...
or how would I go about writing the PHP to output 1 row per project_id and the totals for the day columns.
Attached is a sample report, but it does not correlate with the data in the fiddle.
sample-report.PNG
ASKER
Sorry for not better explaining myself.
The issue I had and your code has is if there are 5 entries in the table for a project id, there will be 5 rows in the output report and that is not what I want.
This might make it easier:
I want 1 row per project_id in the output report.
I want the totals for the particular date.
For project one I want the total hours for 3/10/2014 to be in the Monday column, the total hours for 3/11/2014 to be in the Tuesday column etc.
The issue I had and your code has is if there are 5 entries in the table for a project id, there will be 5 rows in the output report and that is not what I want.
This might make it easier:
I want 1 row per project_id in the output report.
I want the totals for the particular date.
For project one I want the total hours for 3/10/2014 to be in the Monday column, the total hours for 3/11/2014 to be in the Tuesday column etc.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oh, sorry, I didn't understood.
I think you should just remove last line on the group by so only the project id is on your group by clause.
So change this:
to this:
and let me know if this works.
I think you should just remove last line on the group by so only the project id is on your group by clause.
So change this:
$strSQL = 'SELECT
project_id,
FROM_DAYS(TO_DAYS(createdat)) AS createdat,
sum(project_time) AS project_time
FROM
tracking
WHERE
createdat >= \'2014-03-08\'
AND createdat <= \'2014-03-15\'
GROUP BY
project_id,
FROM_DAYS(TO_DAYS(createdat))';
to this:
$strSQL = 'SELECT
project_id,
FROM_DAYS(TO_DAYS(AVG(createdat))) AS createdat,
sum(project_time) AS project_time
FROM
tracking
WHERE
createdat >= \'2014-03-08\'
AND createdat <= \'2014-03-15\'
GROUP BY
project_id;
and let me know if this works.
ASKER
if you do that then only 1 date is returned instead of all dates
Yes, but isn't this what you want? Don't you sant just one record by project?
ASKER
I want 1 project_id row but the totals for each date
Not sure if I understand. If you have these rows:
Project date
1 Today
1 Tomorrow
2 Today
2 Tomorrow
Which rows do you want to be on the resultset?
Project date
1 Today
1 Tomorrow
2 Today
2 Tomorrow
Which rows do you want to be on the resultset?
ASKER
The 2 dimensional array was the key as explained in my post. Thanks to those that tried to help
Open in new window
Hope it helps. Regards.