sql time attendance queries from access control db

  • markrj89

    SSC Rookie

    Points: 29

    Good Day
    I am very new to SQL and am looking for some help to get me going in the right direction.
    I looking to calculate the total hours worked for each employee in my company using the access control database.
    Firstly is this even possible?

    I have used the following query to get the basic information I want to use.

    select
         event_point_id
        ,card_no
        ,time
    from
        acc_monitor_log
    where
        event_point_name in ('Turnstile-1', 'Turnstile-2')
        and card_no = 8630042
    order by
        time desc

        
    The results 
    event_point_id, card_no, time
    1    8630042    2019-02-04 15:59:26.0
    1    8630042    2019-02-04 14:42:19.0
    2    8630042    2019-02-04 13:27:35.0
    1    8630042    2019-02-04 12:58:02.0
    2    8630042    2019-02-04 10:13:44.0
    1    8630042    2019-02-04 09:59:06.0
    1    8630042    2019-02-04 09:58:41.0
    2    8630042    2019-02-04 09:28:44.0
    1    8630042    2019-02-04 09:27:51.0
    2    8630042    2019-02-04 07:27:31.0

    The result I am looking to achieve would be

    card_no, total hours worked today
    8630042  7:45

    Thank you

  • Jeff Moden

    SSC Guru

    Points: 993924

    markrj89 - Monday, February 4, 2019 1:04 PM

    Good Day
    I am very new to SQL and am looking for some help to get me going in the right direction.
    I looking to calculate the total hours worked for each employee in my company using the access control database.
    Firstly is this even possible?

    I have used the following query to get the basic information I want to use.

    select
         event_point_id
        ,card_no
        ,time
    from
        acc_monitor_log
    where
        event_point_name in ('Turnstile-1', 'Turnstile-2')
        and card_no = 8630042
    order by
        time desc

        
    The results 
    event_point_id, card_no, time
    1    8630042    2019-02-04 15:59:26.0
    1    8630042    2019-02-04 14:42:19.0
    2    8630042    2019-02-04 13:27:35.0
    1    8630042    2019-02-04 12:58:02.0
    2    8630042    2019-02-04 10:13:44.0
    1    8630042    2019-02-04 09:59:06.0
    1    8630042    2019-02-04 09:58:41.0
    2    8630042    2019-02-04 09:28:44.0
    1    8630042    2019-02-04 09:27:51.0
    2    8630042    2019-02-04 07:27:31.0

    The result I am looking to achieve would be

    card_no, total hours worked today
    8630042  7:45

    Thank you

    Do the turnstile events represent an In and Out time?  If so, you have a problem with the data collection

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • markrj89

    SSC Rookie

    Points: 29

    Jeff Moden - Tuesday, February 5, 2019 8:55 AM

    markrj89 - Monday, February 4, 2019 1:04 PM

    Good Day
    I am very new to SQL and am looking for some help to get me going in the right direction.
    I looking to calculate the total hours worked for each employee in my company using the access control database.
    Firstly is this even possible?

    I have used the following query to get the basic information I want to use.

    select
         event_point_id
        ,card_no
        ,time
    from
        acc_monitor_log
    where
        event_point_name in ('Turnstile-1', 'Turnstile-2')
        and card_no = 8630042
    order by
        time desc

        
    The results 
    event_point_id, card_no, time
    1    8630042    2019-02-04 15:59:26.0
    1    8630042    2019-02-04 14:42:19.0
    2    8630042    2019-02-04 13:27:35.0
    1    8630042    2019-02-04 12:58:02.0
    2    8630042    2019-02-04 10:13:44.0
    1    8630042    2019-02-04 09:59:06.0
    1    8630042    2019-02-04 09:58:41.0
    2    8630042    2019-02-04 09:28:44.0
    1    8630042    2019-02-04 09:27:51.0
    2    8630042    2019-02-04 07:27:31.0

    The result I am looking to achieve would be

    card_no, total hours worked today
    8630042  7:45

    Thank you

    Do the turnstile events represent an In and Out time?  If so, you have a problem with the data collection

    Hi Jeff
    Thanks for the reply

    Yes, so does the event_point_id 1=out and 2=in.

    Could you explain the data collection problem?

    Thank you

  • Jonathan AC Roberts

    SSCoach

    Points: 16690

    markrj89 - Tuesday, February 5, 2019 10:08 AM

    Jeff Moden - Tuesday, February 5, 2019 8:55 AM

    markrj89 - Monday, February 4, 2019 1:04 PM

    Good Day
    I am very new to SQL and am looking for some help to get me going in the right direction.
    I looking to calculate the total hours worked for each employee in my company using the access control database.
    Firstly is this even possible?

    I have used the following query to get the basic information I want to use.

    select
         event_point_id
        ,card_no
        ,time
    from
        acc_monitor_log
    where
        event_point_name in ('Turnstile-1', 'Turnstile-2')
        and card_no = 8630042
    order by
        time desc

        
    The results 
    event_point_id, card_no, time
    1    8630042    2019-02-04 15:59:26.0
    1    8630042    2019-02-04 14:42:19.0
    2    8630042    2019-02-04 13:27:35.0
    1    8630042    2019-02-04 12:58:02.0
    2    8630042    2019-02-04 10:13:44.0
    1    8630042    2019-02-04 09:59:06.0
    1    8630042    2019-02-04 09:58:41.0
    2    8630042    2019-02-04 09:28:44.0
    1    8630042    2019-02-04 09:27:51.0
    2    8630042    2019-02-04 07:27:31.0

    The result I am looking to achieve would be

    card_no, total hours worked today
    8630042  7:45

    Thank you

    Do the turnstile events represent an In and Out time?  If so, you have a problem with the data collection

    Hi Jeff
    Thanks for the reply

    Yes, so does the event_point_id 1=out and 2=in.

    Could you explain the data collection problem?

    Thank you

    The card is sometimes going out twice without going in.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply