Reporting data

Joan M

Hi all, I've done a small database and PHP based web page to be able to track my working hours easily. Last week a customer asked me how many time in total each month I had spend in one project and I had to get all the times, filter by month manually and sum it all to give him an answer... a matter of 10 minutes, but I want to automate this kind of things... My question is: I will have a table that will be able to be grouped by day, week, month, year or not grouped, but then, I have to get the sum of all hours that I worked in my office, the sum of the hours that I worked out of my office, the sum of the hours travelling... I can only think of doing something like

SELECT sumA,
sumB,
sumC
FROM
(
SELECT sumA,
type,
"0" as sumB,
"0" as sumC
From table1

WHERE type = A

UNION ALL

SELECT sumB,
type,
"0" as sumA,
"0" as sumC
From table1

WHERE type = B

UNION ALL

SELECT sumC,
type,
"0" as sumA,
"0" as sumB
From table1

WHERE type = C
)
AS VIEW1

Is this the way to do it? I want to show the data in columns:

        Hours at the office   |   hours out of the office   |   hours travelling

_____________________________________________________________________________________________________
JANUARY | | |
_____________________________________________________________________________________________________
FEBRUARY | | |
_____________________________________________________________________________________________________
MARCH | | |
_____________________________________________________________________________________________________
APRIL | | |
_____________________________________________________________________________________________________
...

And I would like to solve this in the database part, not creating different tables and then working in the PHP area... Sorry for this way too much generic question, I hope I made it clear enough for you to enlighten me. Thank you in advance!

www.robotecnik.com[^] - robots, CNC and PLC programming<

Mycroft Holmes

Joan M wrote:

And I would like to solve this in the database part, not creating different tables and then working in the PHP area

Wrong tools for the jobs, database for servicing your data query and PHP for the presentation. You could store your queries in a table, relate them to a query/stored proc and expose a title in a combo box for the user to select. Display would need to be an auto generated table to support different result sets. The problem with this is that you may need to support different filtering parameters and you end up with an entire framework to support the reporting when it is faster and simpler to build a result page and query for each report request.

Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP

Joan M

hmmmm... I meant this: I have all the data in the database, create a query and get the results that I show then in the web page using PHP. I have all the "framework" done already, I can tune the queries easily filtering data, ordering and grouping at will... But, maybe it would be much easier to create a "view" in PHPMyAdmin (I still don't know if this can be achieved but I'll look for it) and then the queries would be much easier. After having the view, I would not have to create endless joins to get the data mixed correctly.

www.robotecnik.com[^] - robots, CNC and PLC programming

David Mujica

What database are you using? If it is MS-SQL, then there is a command call PIVOT which will do exactly what you want to do. :java:

Joan M

MariaDB in this case, but thanks I'll take a look at PIVOT and see if there is something similar. :)

www.robotecnik.com[^] - robots, CNC and PLC programming