Help with SELECT

[Guest]

I am currently working on a database that monitors the entry and exit of
outsourced workers. Each worker will generate the maximum of four entries in
the database as follows: Entry (arrival at work), Exit (for lunch), Entry
(return for lunch), Exit (departure from work). I would like to be able to
identify each of the entries for the day as the first, second, third, and
fourth for each of the workers (generate a column carrying intergers
1,2,3,4); then, I would like to be able to identify each entry as
Arrival_at_Work, Exit_for_lunch, Return_from_Lunch, Departure_from_Work. In
addition, I would like to calculate the time each worker used for lunch. How
do I do that? Can somebody help me?

I thank in advance anyone capable of helping me.

 

[Tom Ellison]

Dear E:

You have a pretty fair sized assumption there. Are you saying every
employee will have 4 times recorded? Never 2 or 3?

Now, if there are always exactly 4 times recorded, then you're probably
pretty safe. You could Rank the 4 times per employee per calendar day (they
never work a shift that passes midnight, right?) and use this rank to JOIN
to a table that labels the values 1-4 with "arrival at work", "for lunch",
"return for lunch", and "departure from work".

The ranking isn't too bad. If you'll post a query that shows everything
else you want, I'll try to add the ranking.

I would recommend that you limit this to showing only those employee/date
combinations that do have exactly the 4 times recorded. If someone has 2
times recorded, they would be arrival and departure and skipping lunch,
perhaps.

Tom Ellison

 

[Guest]

Dear T:

Actually, there will be instances when an employee will have only two or
three entries. Also, there will be cases when an employee will stay beyond
the regular hours of work. Currently, there isn’t a limitation of time to log
into the system. Below, I am posting the query that generated my question:

SELECT user_name, Min(login_date_time) AS login_first_entry,
Max(login_date_time) AS
login_last_exit,TIMEDIFF(Max(login_date_time),Min(login_date_time)) AS
Hours_Worked, If (TIMEDIFF(Max(login_date_time),Min(login_date_time))>
CAST('6:00:00' AS Time),
TIMEDIFF((TIMEDIFF(Max(login_date_time),Min(login_date_time))),CAST('1:30:00'
AS Time)), TIMEDIFF(Max(login_date_time),Min(login_date_time))) AS
Hours_Worked_Minus_Lunch FROM tb_user INNER JOIN tb_login ON user_ID =
login_user_id
GROUP BY user_name, login_user_id,day(login_date_time);

Thanks

 

[Tom Ellison]

Dear E:

I have edited your query according to my reading preferences, and added the
Rank column for which you asked:

SELECT user_name, Min(login_date_time) AS login_first_entry,
Max(login_date_time) AS login_last_exit,
TIMEDIFF(Max(login_date_time),
Min(login_date_time)) AS Hours_Worked,
If (TIMEDIFF(Max(login_date_time), Min(login_date_time)),
CAST('6:00:00' AS Time),
TIMEDIFF((TIMEDIFF(Max(login_date_time),
Min(login_date_time))), CAST('1:30:00' AS Time)),
TIMEDIFF(Max(login_date_time),
Min(login_date_time))) AS Hours_Worked_Minus_Lunch,
(SELECT COUNT(*)
FROM tb_login T
WHERE T.login_user_id = tb_login.login_user_id
AND DateValue(T.login_date_time) =
DateValue(tb_login.login_date_time)
AND T.login_date_time < tb_login.login_date_time) + 1
AS Rank
FROM tb_user
INNER JOIN tb_login ON user_ID = login_user_id
GROUP BY user_name, login_user_id, day(login_date_time)

This will rank the rows within each set for one login_user_id that fall in
the same 24 hour day, according to the times within that day.

To limit these to those with exactly 4 entries for that date, you could add
this at the end:

HAVING COUNT(*) = 4

Similarly, you could have sets for days with other number of entries. For
example, for those with 2 entries you could change the + 1 to + 11 and use
HAVING COUNT(*) = 2. The Rank values for these would then be 11 and 12.

I'm thinking you could have a table with values 1, 2, 3, and 4 plus 11 and
12. In this table you could record text like:

1 Entry (arrival at work)
2 Exit (for lunch)
3 Entry (return for lunch)
4 Exit (departure from work)
11 Entry (arrival at work)
12 Exit (departure from work)

A union of the different sets thus produced might be just the thing to
finish the query. Make sense?

Tom Ellison

 

[Guest]

Dear T:

Thanks for the solution! I have created the rank; however, I am not quite
sure how to calculate the amount of "time used for lunch" using your query
as the backbone.

E.

 

[Tom Ellison]

Dear E:

What did you implement? Do you have the Rank values I suggested as 1, 2, 3,
4, 11, and 12? Or what?

The query to find lunches for those who have a 2 and 3 row in the query
would JOIN the query results on themselves to find the two rows, and take
the difference. I'll give more detail when you have answered my question
here.

Tom Ellison