Total Hours Per Pay Period

[rdivilbiss]

I have a time clock application.

The timeclock table is:
 

Code:

id int(11)
userid varchar(25)
timein datetime
timeout datetime
worked decimal(4,2)

To get a list of the dates and time worked for each day for a pay period I can do:

Code

Language: sql (GeSHi-highlighted)
SELECT `userid`, date(`timeout`), sum(`worked`) AS sum
FROM `timeclock` 
WHERE `userid`='jdoe' AND date(`timeout`) BETWEEN '2008-03-26' AND '2008-04-10' 
GROUP BY DATE(`timeout`) 
ORDER BY `timeout`;

Can this be modified to also return the entire total sum of time worked without having to run another query?

[CrYpTiC_MauleR]

Ok so each row is just a single time punch and above query is getting all punches for a specific time period. So are you asking how to get the time punches for that period all added together?

[VGR]

the answer is "NO" ; you can probably do it in one signle "query" with a sub-query (so in fact, two queries) but your ODBC driver wouldn't like it (I guess you use ODBC because of the silly unnecessary backquotes around column names)

given you've in fact two queries to run, you'd better separate  them. The DB engine is building a temporary table anyway, and in case of the subquery an other table. So build tables yourself, unless you want to rely on the query cache to speed up repetitive queries for results  ;-)

you could add a column to each user containing - if not the current day - that total amount and thus a simple sum() on that user would give you the total and a simple sum() on all users would give the grand total, all perperiods

or you could build a separate table "user_time" to hold user_id and total per day, then use it as above.

[rdivilbiss]

Thank you both for your response.

I came to the same conclusion, so I wrote a  procedure and put the results in a temporary table, then query from that.  Actually, then I'm running several queries but the results are in single digit milliseconds and it is a low use application, so that will do nicely.

As to ODBC, never use it. I do however use prepared statements for all my web forms and prefer the formality of the back tics.