Re: Query w/sum and GROUP BY for related tables?
Thank you for your response. I've come up with a query that is working for me before seeing your post. I didn't quite understand when you said
"put the logic to record the t_out value for a given t_in in a trigger"
Here is the query I have working. I've hard coded the date for now for testing. Do you see any issues with this query, or do you recommend me to structure it like your suggestion.
SELECT CONCAT(e.last, ', ', e.first) AS name, DATE_FORMAT(p.tistamp, '%m/%d/%y') as d2,
ROUND(
(
(SELECT SUM(Case WHEN status='OUT' THEN (TIME_TO_SEC(tistamp)) ELSE 0 END )AS Outime)
-
(SELECT SUM(Case WHEN status='IN' THEN (TIME_TO_SEC(tistamp)) ELSE 0 END )AS Intime)
)
/3600, 2) AS daily_hours
FROM punches p, employees e
WHERE p.emp_id=e.id
AND DATE_FORMAT(tistamp, '%m/%d/%y')='05/25/16'
GROUP BY p.emp_id
Thanks for your Time!
Subject
Written By
Posted
Re: Query w/sum and GROUP BY for related tables?
August 09, 2016 09:53AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.