sql time attendance queries from access control db

Question

sql time attendance queries from access control db

markrj89

SSC Enthusiast

Points: 129
More actions
February 4, 2019 at 1:04 pm

#370529

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

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

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

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

markrj89 SSC Enthusiast Points: 129 More actions · Answer 2

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: 18257 More actions · Answer 3

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.