# question

## 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
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
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?
1 Like 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.,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.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.