I still need help w/ this if anyone can. I'm trying to calculate break times from this table
Tables
staff_log_empl_num................ text
staff_log_date....................... date/time
time_in_time_out..................... text.... "in"; "break out"; "break in"; "out"
staff_log_time........................ date/time
This query (Query 1) gives me all login/logout data from employees that didn't take a break w/ no problem
Code:
SELECT sl1.staff_log_date, sl1.staff_log_empl_num, (staff_first_name&' '& staff_last_name) AS staff_full_name, sl1.staff_log_time, sl1.time_in_time_out, sl2.staff_log_time, sl2.time_in_time_out, (datediff("s",sl1.staff_log_time, sl2.staff_log_time)/60.00)/60 AS total_time
FROM (staff_login AS sl1INNER JOIN staff_login AS sl2 ON (sl1.staff_log_empl_num = sl2.staff_log_empl_num) AND (sl1.staff_log_date = sl2.staff_log_date))
INNER JOIN staff AS s ON sl1.staff_log_empl_num = s.staff_empl_num
WHERE sl1.time_in_time_out = 'IN' and sl2.time_in_time_out = 'OUT'
ORDER BY sl1.staff_log_date DESC;
Here is an example of Query 1 the result set
staff_log_date |
staff_log_empl_num |
staff_full_name |
sl1.staff_log_time |
sl1.time_in_time_out |
sl2.staff_log_time |
sl2.time_in_time_out |
total_time |
8/13/2012 |
005319 |
STAFFER1 STAFFER1 |
9:00 AM |
IN |
5:00 PM |
OUT |
8 |
8/12/2012 |
000001 |
JEFF STAFFER |
9:00 AM |
IN |
5:00 PM |
OUT |
8 |
8/12/2012 |
005319 |
STAFFER1 STAFFER1 |
9:00 AM |
IN |
5:00 PM |
OUT |
8 |
8/11/2012 |
005319 |
STAFFER1 STAFFER1 |
9:00 AM |
IN |
5:00 PM |
OUT |
8 |
8/11/2012 |
000001 |
JEFF STAFFER |
9:00 AM |
IN |
5:00 PM |
OUT |
8 |
8/10/2012 |
005319 |
STAFFER1 STAFFER1 |
9:00 AM |
IN |
5:00 PM |
OUT |
8 |
8/10/2012 |
000001 |
JEFF STAFFER |
9:00 AM |
IN |
5:00 PM |
OUT |
8 |
8/9/2012 |
005319 |
STAFFER1 STAFFER1 |
9:00 AM |
IN |
5:00 PM |
OUT |
8 |
This query (Query 2) gives me all login/logout data from employees that did take a break w/ no problem
Code:
SELECT sl1.staff_log_date, sl1.staff_log_empl_num, (staff_first_name&' '& staff_last_name) AS staff_full_name, sl1.staff_log_time, sl1.time_in_time_out, sl3.staff_log_time, sl3.time_in_time_out, sl4.staff_log_time, sl4.time_in_time_out, sl2.staff_log_time, sl2.time_in_time_out, round((datediff("s", (sl3.staff_log_time - sl4.staff_log_time), (sl1.staff_log_time - sl2.staff_log_time))/60)/60,2) AS total_time
FROM (((staff_login AS sl1INNER JOIN staff_login AS sl2 ON (sl1.staff_log_empl_num = sl2.staff_log_empl_num) AND (sl1.staff_log_date = sl2.staff_log_date))
INNER JOIN staff_login AS sl3 ON (sl1.staff_log_empl_num = sl3.staff_log_empl_num) AND (sl1.staff_log_date = sl3.staff_log_date))
INNER JOIN staff_login AS sl4 ON (sl1.staff_log_empl_num = sl4.staff_log_empl_num) AND (sl1.staff_log_date = sl4.staff_log_date))
INNER JOIN staff AS s ON sl1.staff_log_empl_num = s.staff_empl_num
WHERE sl1.time_in_time_out = 'IN' and sl3.time_in_time_out = 'BREAK OUT' and sl4.time_in_time_out = 'BREAK IN' and sl2.time_in_time_out = 'OUT'
ORDER BY sl1.staff_log_date DESC;
Here is an example of Query 2 the result set
staff_log_date |
staff_log_empl_num |
staff_full_name |
sl1.staff_log_time |
sl1.time_in_time_out |
sl3.staff_log_time |
sl3.time_in_time_out |
sl4.staff_log_time |
sl4.time_in_time_out |
sl2.staff_log_time |
sl2.time_in_time_out |
total_time |
6/14/2012 |
008548 |
JAISON WILLIAMS |
7:06 PM |
IN |
9:00 PM |
BREAK OUT |
10:00 PM |
BREAK IN |
11:59 PM |
OUT |
3.87 |
6/7/2012 |
005319 |
STAFFER1 STAFFER1 |
8:00 AM |
IN |
1:45 PM |
BREAK OUT |
3:00 PM |
BREAK IN |
5:00 PM |
OUT |
7.75 |
I have tried doing a union and adding this WHERE clause
Code:
WHERE (sl1.time_in_time_out = 'IN' and sl3.time_in_time_out = 'BREAK OUT' and sl4.time_in_time_out = 'BREAK IN' and sl2.time_in_time_out = 'OUT') or (sl1.time_in_time_out = 'IN' and sl2.time_in_time_out = 'OUT' and sl1.time_in_time_out = sl3.time_in_time_out and sl2.time_in_time_out <>sl4.time_in_time_out AND sl3.staff_log_time>=sl1.staff_log_time and sl3.time_in_time_out )
this is a sample of what I get (notice the 4 records each of the employees that took breaks. I'm trying to get just 1 record per day)
staff_log_date |
staff_log_empl_num |
staff_full_name |
sl1.staff_log_time |
sl1.time_in_time_out |
sl3.staff_log_time |
sl3.time_in_time_out |
sl4.staff_log_time |
sl4.time_in_time_out |
sl2.staff_log_time |
sl2.time_in_time_out |
total_time |
7/10/2012 |
005319 |
STAFFER1 STAFFER1 |
9:00 AM |
IN |
9:00 AM |
IN |
9:00 AM |
IN |
3:00 PM |
OUT |
6 |
7/10/2012 |
000001 |
JEFF STAFFER |
9:00 AM |
IN |
9:00 AM |
IN |
9:00 AM |
IN |
3:00 PM |
OUT |
6 |
7/9/2012 |
005319 |
STAFFER1 STAFFER1 |
9:00 AM |
IN |
9:00 AM |
IN |
9:00 AM |
IN |
3:00 PM |
OUT |
6 |
7/9/2012 |
000001 |
JEFF STAFFER |
9:00 AM |
IN |
9:00 AM |
IN |
9:00 AM |
IN |
3:00 PM |
OUT |
6 |
7/8/2012 |
005319 |
STAFFER1 STAFFER1 |
9:00 AM |
IN |
9:00 AM |
IN |
9:00 AM |
IN |
3:00 PM |
OUT |
6 |
7/8/2012 |
000001 |
JEFF STAFFER |
9:00 AM |
IN |
9:00 AM |
IN |
9:00 AM |
IN |
3:00 PM |
OUT |
6 |
7/7/2012 |
000001 |
JEFF STAFFER |
9:00 AM |
IN |
9:00 AM |
IN |
9:00 AM |
IN |
5:00 PM |
OUT |
8 |
7/7/2012 |
005319 |
STAFFER1 STAFFER1 |
9:00 AM |
IN |
9:00 AM |
IN |
9:00 AM |
IN |
5:00 PM |
OUT |
8 |
6/14/2012 |
008548 |
JAISON WILLIAMS |
7:06 PM |
IN |
9:00 PM |
BREAK OUT |
10:00 PM |
BREAK IN |
11:59 PM |
OUT |
3.87 |
6/14/2012 |
008548 |
JAISON WILLIAMS |
7:06 PM |
IN |
7:06 PM |
IN |
10:00 PM |
BREAK IN |
11:59 PM |
OUT |
1.98 |
6/14/2012 |
008548 |
JAISON WILLIAMS |
7:06 PM |
IN |
7:06 PM |
IN |
9:00 PM |
BREAK OUT |
11:59 PM |
OUT |
2.98 |
6/14/2012 |
008548 |
JAISON WILLIAMS |
7:06 PM |
IN |
7:06 PM |
IN |
7:06 PM |
IN |
11:59 PM |
OUT |
4.87 |
6/7/2012 |
005319 |
STAFFER1 STAFFER1 |
8:00 AM |
IN |
8:00 AM |
IN |
1:45 PM |
BREAK OUT |
5:00 PM |
OUT |
3.25 |
6/7/2012 |
005319 |
STAFFER1 STAFFER1 |
8:00 AM |
IN |
8:00 AM |
IN |
8:00 AM |
IN |
5:00 PM |
OUT |
9 |
6/7/2012 |
005319 |
STAFFER1 STAFFER1 |
8:00 AM |
IN |
8:00 AM |
IN |
3:00 PM |
BREAK IN |
5:00 PM |
OUT |
2 |
6/7/2012 |
005319 |
STAFFER1 STAFFER1 |
8:00 AM |
IN |
1:45 PM |
BREAK OUT |
3:00 PM |
BREAK IN |
5:00 PM |
OUT |
7.75 |
As you can see by this long post, this is becoming a thorn in my side. The times are captured by time stamp. Her employees enter their employee numbers on a form, specify whether they are logging in/out or going out/coming in from break and then they hit "enter". This captures their info. All of her employees do not take breaks, so I'm trying to get these two queries on one query so I can give her a report. Can ANYONE help me?
Sincerely,
Frustrated