| req solution [message #377095] |
Sat, 20 December 2008 02:09  |
sweetkhaliq
Messages: 200
Registered: April 2006
|
Senior Member |
|
|
Dear Members
I am working on attendance system. I want that employee is automatically mark out by system after the 16 hours after he came in if he did not mark out.
suppose emloyee code 1033 employee come in at 09:00 and he did not make attendance for out. The system will automatically out this employee after 16 hours.
The attendance table is as like this
emp_code varchar2(6),
atten_date date,
time_in date,
time_out date
Thanks & regards
|
|
|
|
|
|
| Re: req solution [message #377102 is a reply to message #377095] |
Sat, 20 December 2008 03:11  |
dhanuka.rajesh
Messages: 49
Registered: March 2008
Location: Mumbai
|
Member |
|
|
You can add a column, EMP_SYS_TIME_OUT Date to your table.
emp_code varchar2(6),
atten_date date,
time_in date,
time_out date
EMP_SYS_TIME_OUT Date
Now when a Employee signs in the system you calculate its sign out time i.e. Sign in time + 16 hours.
If you are using oracle forms or any front end, write a validation, employee cannot sign off from system after EMP_SYS_TIME_OUT is crossed.
See if this approach works for you.
Let me know if there are further queries on this.
|
|
|
|
| Re: req solution [message #377110 is a reply to message #377095] |
Sat, 20 December 2008 05:06 |
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
|
Senior Member |
|
|
@dhanuka
WHEN will the validation be fired? How to know it will fire after 16 hours if he doesn't use JOBS??
Rajy
|
|
|
|
| Re: req solution [message #377113 is a reply to message #377110] |
Sat, 20 December 2008 05:18 |
dhanuka.rajesh
Messages: 49
Registered: March 2008
Location: Mumbai
|
Member |
|
|
We need that if Employee does not sign out after 16 hours system automatically marks him sign off.
There are two approaches to the question mentioned.
1. User database jobs and schedule it on regular intervals or
fixed time, which will check for log out time and then update
according the requirements.
2.
Table Structure:
emp_code varchar2(6),
atten_date date,
time_in date,
time_out date
EMP_SYS_TIME_OUT Date
When user signs on store sign date time + 16 hours in
table(EMP_SYS_TIME_OUT).
When user signs off manually check his system sign off time
(SYSDATETIME).
If (SYSDATETIME) is greater than system sign off time prompt
user a error message
"System sign off done" and update (time_out) column time with
system sign off time(EMP_SYS_TIME_OUT).
I hope this helps.
Dhanuka. Rajesh
|
|
|
|
| Re: req solution [message #377233 is a reply to message #377113] |
Mon, 22 December 2008 01:01 |
sweetkhaliq
Messages: 200
Registered: April 2006
|
Senior Member |
|
|
Thanks for reply
I have done as you say. I have added the column sys_out in my attendance table but i have faced a problem.
emp_code atten_date time_in time_out sys_out
001174 12/22/2008 12/22/2008 9:04:07 AM 12/22/2008 11:50:07 AM 12/23/2008 1:04:07 AM
001174 12/22/2008 12/22/2008 12:50:07 PM
in our company a employee can come in and out more than once time. I have placed the sys_out time when employee first time come in. But when he go out and again come in i have not placed any value in sys_out field because this value is added only one time when he first time come in. when he second time come in and after he forget to sign out, the system should automatically sighn out to this second rows time_out field.
Please see the above table data will help out to understand the problem.
Thanks and regards
[Updated on: Mon, 22 December 2008 01:46] Report message to a moderator |
|
|
|
| Re: req solution [message #377259 is a reply to message #377233] |
Mon, 22 December 2008 02:59 |
dhanuka.rajesh
Messages: 49
Registered: March 2008
Location: Mumbai
|
Member |
|
|
As per your specification,
1. Multiple Sign in & Sign of possible in one day.
2. System to sign off employee based on his sign in time for
last sign on done in system.
Following steps can be taken to avoid complications.
1. Employee cannot sign in system unless signed off for the day.
(Prompt a error message saying cannot sign on as employee has
not signed off)
2. System Sign off time should be updated on each log in.
(With this there can always be only one record where the user
might not have signed off)
I have one query, What sign out time must be used by system.
I.e. Employee signed in system at 12/22/2008 9:04:07 AM
System Out time calculated is 12/23/2008 1:04:07 AM
Now user has signed off at 12/22/2008 11:50:07 AM and
sign in system again at 12/22/2008 12:50:07 PM.
My query is, when employee signs in system at 12/22/2008 12:50:07 PM time what should be the system sign off time?
Otpions.
A - 12/22/2008 12:50:07 PM + 16 hours
B - 12/22/2008 9:04:07 AM + 16 hours
(First sign on time + 16hours)
Note: - Also let me know if there is scope to change table
design at data flow and logical processing level.
(Like we can have head detail tables or separate set of
tables to maintain separate data)
Dhanuka. Rajesh
|
|
|
|
|
|
| Re: req solution [message #377283 is a reply to message #377280] |
Mon, 22 December 2008 04:03 |
dhanuka.rajesh
Messages: 49
Registered: March 2008
Location: Mumbai
|
Member |
|
|
If user signs on second time,
write a cursor to get System sign out time for that day (first sign on record) and update the system sign out column with that time.
Ok.
This should resolve your issue.
Dhanuka. Rajesh
|
|
|
|
| Re: req solution [message #377415 is a reply to message #377283] |
Mon, 22 December 2008 22:55 |
sweetkhaliq
Messages: 200
Registered: April 2006
|
Senior Member |
|
|
Yes your solution is quite valid if the employee sign in and sign out in the same day. But it is not valid if an employee has come in current date and has sign out on next day and again sign in next day. See the below diagram
emp_code atten_date time_in time_out sys_out
002277 12/22/2008 12/22/2008 11:04:07 PM 12/23/2008 01:50:07 AM 12/23/2008 3:04:07 PM
002277 12/23/2008 12/23/2008 02:40:07 AM
My query is
(1) I have placed in sys_out field (time_in+16/24) = 12/23/2008 3:04:07 PM when the employee 1st time sign in. How can i place the same value when employee sign in 2nd or 3rd time. Because i have placed it only one time when the employee first time sign in. It can solve my problem.
(2) How can i place value in sys_out the employee who sign in in current date and sign out in next date and again sign in next date. Because the employee total in time = 8 hours.
thanks & regards
[Updated on: Tue, 23 December 2008 02:01] Report message to a moderator |
|
|
|
|
|
| Re: req solution [message #378116 is a reply to message #378110] |
Mon, 29 December 2008 00:26 |
dhanuka.rajesh
Messages: 49
Registered: March 2008
Location: Mumbai
|
Member |
|
|
Select Max(System Sign out time) from table.
If (New sign on time) < (Max(System Sign Out time)) Then
New System Sign Out time = MAx(System Sign Out Time);
Elsif (New Sign on time) >= (Max(System Sign out time)) then
New System Sign Out time = (New System Sign Out time) + 16 hrs.
End If;
This will resolve the issue even if the dates change and sign on and sign off are handled in two diff working dates.
Regards,
Dhanuka. Rajesh
|
|
|
|
|
|
| Re: req solution [message #378172 is a reply to message #378161] |
Mon, 29 December 2008 03:57 |
dhanuka.rajesh
Messages: 49
Registered: March 2008
Location: Mumbai
|
Member |
|
|
Select MIN(SIGN IN TIME), MAX(SIGN OUT TIME)
From Table
Where EMP_ID = XXX
Group by (SYSTEM SIGN OUT DATE TIME)
This query will return Mininum sign on time and Maximum Sign off time during the day.
I.e 16 hours.
I have some queries:
1. For how many hours a employee works generally.
2. What if user does a double shift, i mean he generally is
suppose to work for X number of hours in a day.
Now he is working for 2X number of hours in same day (Continous)
Best Regards,
Dhanuka. Rajesh
|
|
|
|
|
|
|
|
| Re: req solution [message #379385 is a reply to message #378735] |
Tue, 06 January 2009 04:55 |
dhanuka.rajesh
Messages: 49
Registered: March 2008
Location: Mumbai
|
Member |
|
|
Please give details for your query...
I am sorry for the inconvenience caused as i was out of town not able to check my mails.
Regards,
Dhanuka. Rajesh
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
