Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2012
    Posts
    7

    Unanswered: HELP (first in / last out each day for each employee)

    I have a database that tracks when each employee swipes in/out each day. I am trying to produce a report that will show the first time each employee swipes in each day and the last time an employee swipes out each day. I have a field with the date_time (together) "1/1/2012 9:56:53 AM" and employee_id. There are many swipes by each employee each day, but only need first and last for each employee.

    Any suggestions for this fairly new person who is just learning how to provide this report?

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Have a look at getting the min and max date time per day per employee

    Code:
    SELECT empid, date_format(date_time, '%Y%m%d') date_time, min(date_time), max(date_time)
    FROM table
    GROUP BY empid, date_format(date_time, '%Y%m%d');
    What this does is returns for each employee for each day the minimum time i.e. the time that someone swiped in and the max time i.e. the time that they swiped out at the end of the day.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Apr 2012
    Posts
    7
    I am getting a syntax error with this.

    Here is what I have entered as the statement:

    SELECT emp_id, date_format(date_time,'%Y%m%d') date_time, min(date_time), max(date_time)
    FROM Sheet1
    GROUP BY emp_id, date_format(date_time,'%Y,%m$d');

    I get this error upon execution:

    "syntax Error (missing oeprator) in query expression 'date_format (date_time, '%y$m%d) time_date

    Your guidance is appreciated!

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Thread bump. Last post got caught in moderation queue!
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2012
    Posts
    7
    Sorry for the typo error on the last one. This is what I have and is creating the error.

    SELECT emp_id, date_format(date_time, '%Y%m%d') date_time, min(date_time), max(date_time)
    FROM Sheet1
    GROUP BY emp_id, date_format(date_time, '%Y%m%d');

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you please copy and display the entire error message
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2012
    Posts
    7
    Copy of error. Thanks!
    Attached Thumbnails Attached Thumbnails error.png  

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i thought so -- it didn't sound like a mysql error

    you do realize, i hope, that microsoft access and oracle mysql are completely different systems
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by MarkAK View Post
    ...
    ... I have a field with the date_time (together) "1/1/2012 9:56:53 AM" and employee_id. ...
    ...
    What is the datatype of date_time field?
    If it was a sring type(CHAR or VARCHAR), by what format were the values stored?

Posting Permissions

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