|
|
|
Re: Urgent Help for 4 Shift Attendance procedure. [message #285390 is a reply to message #284256] |
Tue, 04 December 2007 06:32   |
rumman
Messages: 48 Registered: June 2007 Location: Bangladesh
|
Member |
|
|
I am describing how we did the solution :
We have a view ATTN_SCHEDULE_V;
desc ATTN_SCHEDULE_V
Output :
emp_no number(30),
shift_id varchar2(30),
schedule_from date,
schedule_to date,
in_time date,
out_time date
.... ... others
Values :
1 - A - 14/12/2007 - 15/12/2007 - 14/12/2007 23:00:00 - 15/12/2007 07:00:00
1 - A - 15/12/2007 - 15/12/2007 - 15/12/2007 01:00:00 - 15/12/2007 22:00:00
2 - A - 14/12/2007 - 15/12/2007 - 14/12/2007 23:00:00 - 15/12/2007 07:00:00
We have a procedure that gives the schedule date as attendance date with the following algorithm :
Function F_ATTN_DATE(p_Emp_no number, p_Date date)
/* p_Emp_No := 1
p_Date := SYSDATE
*/
-----------------------------
1. Find the in_time from ATTN_SCHEDULE_V where the p_Date is between in_time and out_time.
if found then
return in_time;
end if;
2. Find the max(in_time) as in_time from ATTN_SCHEDULE_V where trunc(p_Date) between schedule_from and schedule_to and in_time <= p_Date;
if found then
return in_time;
end if;
3. Find the min(in_time) as in_time from ATTN_SCHEDULE_V where trunc(p_Date) between schedule_from and schedule_to and in_time > p_Date;
if found then
return in_time;
end if;
end of function F_ATTN_DATE
-----------------------
Now the above function gives us the scheduled in_time. During insertion into ATTENDANCE table if a record already exists for a particular emp_no on the F_ATTN_DATE then this record treated as movement and if record not found in the attendance table then it is new entry to the office on the return of F_ATTN_DATE.
Please, ask me if you dont understand any part of my answer. Our attendance system is working perfectly internaly (in our office) and externally (in customers' offices)
|
|
|
|