asked
How to manipulate time for time card function
I have a time card data store on a database like this and as you can see it stores the data for every time someone punches in or out on a separate line. id user_id clock_in_stamp clock_out_stamp 1 13 2016-12-13 09:10:42 2016-12-13 14:13:00 2 13 2016-12-13 14:43:38 2016-12-13 20:31:20 I am trying to get the output to look like this User clock_in clock_out_for_Lunch clock_in_for_Lunch clock_out total_hours_worked John Doe 2016-12-13 09:10:42 2016-12-13 14:13:00 2016-12-13 14:43:38 2016-12-13 20:31:20 sum of hours Any insight on how I do this would be great. Thank you in advance
How would you define lunch? Is it the first clock out of the day? What if someone doesn't take lunch? What if someone works a half day?
Assumption: Max is 2 clock ins a day. Use a UNION. SELECT User, Clock_In, NULL, NULL, Clock_Out FROM Tbl WHERE Count(DATE(Clock_In)) = 1 UNION SELECT Min(User), Min(Clock_In), Min(Clock_Out), Max(Clock_In), Max(Clock_Out) FROM tbl GROUP BY User, DATE(Clock_In) HAVING Count(DATE(Clock_In)) > 1.,Assumption: Max is 2 clock ins a day. Use a UNION. SELECT User, Clock_In, NULL, NULL, Clock_Out FROM Tbl WHERE Count(DATE(Clock_In)) = 1 UNION SELECT Min(User), Min(Clock_In), Min(Clock_Out), Max(Clock_In), Max(Clock_Out) FROM tbl GROUP BY User, DATE(Clock_In) HAVING Count(DATE(Clock_In)) > 1. ,I would use a UNION. Select USER, Clock_In, NULL, NULL, Clock_Out FROM Tbl WHERE count(date(clock_in)) =1 The 2nd half would be a bit more complex using a pivot table so that you can fill in Clock_In, Out_For_Lunch, In_For_Lunch and Clock_Out. The where clause would be the WHERE count(date(clock_in)) = 2. Keep in mind this wont include logic for someone that ends up with more that 2 records unless you add additional logic and make the second where clause > 1.
Sorry, the first response got all nasty with failed login attempts and such... Assumption: Max is 2 clock ins a day. Use a UNION. SELECT User, Clock_In, NULL, NULL, Clock_Out FROM Tbl WHERE Count(DATE(Clock_In)) = 1 UNION SELECT Min(User), Min(Clock_In), Min(Clock_Out), Max(Clock_In), Max(Clock_Out) FROM tbl GROUP BY User, DATE(Clock_In) HAVING Count(DATE(Clock_In)) > 1.
18 People are following this question.
