Hi,

Working with hourly payroll data; the payroll company does not provide day hours as such. It must be calculated in other ways.

It provides Total Hours, Afternoon Hours and Evening Hours etc, so the Afternoon and Evening Hours must be added and then subtracted from the Total Hours to arrive at Day Hours.

I have managed to get these totals from an .csv file using Phpmyadmin and Mysql on Ubuntu 12.04, using the following query :

Where I am stuck is how to calculate "Day Hours" by subtracting from Total Hours the other sums all in one query.

Also, if someone could recommend a good primer for novices on SQL math it would be appreciated.

Thanks in advance !

Dear ceyx:
I would try something like this: (please check grammar)

SELECT sum( `Reg Hourly Units` ) AS Total_Hours, sum(`Wknd Aft Prem Units` ) AS WkEnd_Aft_Hours, sum( `Wknd Night Prem Units` ) AS WkEnd_Night, sum( `Wknd Day Prem Units` ) AS Weekend_Day_hours, sum( `Wknd Aft Prem Units` ) AS Weekend_Afternoon, sum( `Wknd Night Prem Units` ) AS Weekend_Night_hours, (TotalHours - WkEnd_Aft_Hours - WkEnd_Night - Weekend_Day_hours - Weekend_Afternoon - Weekend_Night_hours) AS DayHours
FROM `PP1R`
WHERE `Occupation` = 'Tech'
AND `Home Department Name` = 'XXX'

I would no use double quotes because it depends on mysql version. Don´t remember if you have to create the fields in the database.
I used something like this using mysql and php. I takes time until it works. Is very tricky.
Tell me if there is any error. If there is no way to make it work, please ask.

Alejandro.

1 members found this post helpful.

Hello Alejandro !

Thanks for your response. You certainly pointed me in the right direction.

The error message that was returned from your suggestion above :

A quick search had me placing single quotes around the suggested fields like so :

And it worked . The math was off but that is because my feilds were hastily chosen - but the engine works !

Thanks for your assistance.

Regards

Dear ceyx:
Thanks for the point.
There is a manual you can check for maths.
I downloaded it from mysql. Don't know if it is still available.
I tried to upoload it but I couldn't.

Alejandro