question

craig121616 avatar image
craig121616 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
sqltime
1 comment
10 |1200

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 ·
mjsimmons19 avatar image
mjsimmons19 answered
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.
10 |1200

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

mjsimmons19 avatar image
mjsimmons19 answered
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.
10 |1200

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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