Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2007
    Posts
    3

    Unanswered: Query from access log

    Hi,

    I need help with query. I have access log table something like this:

    session_id (int) | user (varchar) | login_time (datetime) | logout_time (datetime)
    1 | JOHN | 2007-03-02 15:32:01 | 2007-03-02 18:01:55
    2 | JOHN | 2007-03-09 11:43:33 | 2007-03-09 21:02:25
    3 | JOHN | 2007-03-15 22:00:05 | 2007-03-16 06:37:21
    4 | MIKE | 2007-04-02 12:32:01 | 2007-03-02 14:01:55
    5 | MIKE | 2007-03-09 10:41:33 | 2007-03-09 20:44:15



    For a result I need report of working hours (user is a employee). The first idea was:

    Code:
    SELECT `user`, `login_time`, `logout_time`,
    TIMEDIFF(`logout_time`, `login_time`) FROM `access_log`
    ORDER BY `user`, `login_time`;
    But access_log table may contain rows with a same time period (per user). For a example when user enters to the system twice from different PC-s (I can not disallow this), then this query gives double working hours

    So I need somehow to take into consideration user time intersection.

    Thank you in advance.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by fiff
    But access_log table may contain rows with a same time period (per user).
    would you mind showing some sample data that illustrates this
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2007
    Posts
    3
    Quote Originally Posted by r937
    would you mind showing some sample data that illustrates this
    Data:
    MIKE | 13.05.2007 2:00 | 13.05.2007 7:00 -> 5h
    MIKE | 13.05.2007 5:00 | 13.05.2007 9:00 -> 4h


    I need result:
    MIKE | 13.05.2007 | 7h -> 5+4-2=7h, 2 hours (5:00-7:00) is intersection

    In resultset I dont need login_time and logout_time, I just need working hours per day.

    Thank you for reply!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    wow

    off the top of my head i will recommend that you write an application script to detect these situations

    doing it with sql alone is going to be very, very complex
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2007
    Posts
    3
    Ok, but can you give me some ideas for this query/queries. I dont hope that you compile whole query for me.

    Is there a way to get this time intersection (common period) per day? Then it's easy to calculate hours in application. As I understand it's possible with JOIN?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by fiff
    Ok, but can you give me some ideas for this query/queries.
    i've done a lot of sql in my day, but i don't even want to try this one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •