Home » SQL & PL/SQL » SQL & PL/SQL » Urgent Help for 4 Shift Attendance procedure.
icon4.gif  Urgent Help for 4 Shift Attendance procedure. [message #284256] Thu, 29 November 2007 04:27 Go to next message
ziauldba
Messages: 55
Registered: January 2007
Location: Bangladesh
Member

Dear.........
I need help for a procedure.....
In my company(factory) Here is four shift

A Shift 7:00 to 15:00
B Shift 15:00 to 23:00
C Shift 23:00 to 7:00
General Shift 8:00 to 17:00

and we want to use RTA 600/700 machine for attendance.
and which is already used in our head office(there is single shift).
____________________________________________________
<><><><><Head office procedure<><><><><><><><
____________________________________________________
RTA 600/700 machine every generate one text file which we load by
SQL loader to database
-----------------control file and data of sql loader----------
LOAD DATA
INTO TABLE HM_ATND_RAW REPLACE
FIELDS TERMINATED BY ':'
(MACH_NO "to_char(:MACH_NO,'fm099')",
CARD_NO "to_char(:CARD_NO,'fm0999999999')",
ATND_DATE "to_date(:ATND_DATE,'RRRRMMDD')",
ATND_TIME "to_char(:ATND_TIME,'fm099999')")
--------------------------------------------
sample data generate from machine
------------------------------------------
001:0009580532:20050921:192642
001:0009580483:20050921:193014
001:0009580468:20050921:201455
001:0009580530:20050921:205116
001:0001207205:20050921:205327
001:0009580530:20050922:081236
001:0001207205:20050922:082038
001:0009580471:20050922:082134
001:0009580483:20050922:083057
001:0009580533:20050922:083105
001:0009580596:20050922:083405
-------------------------------------
after that we run the procedure which i attach in doc file.
__________________________________________________________
<><><><>My problem for develope 4 shift<><><><<
_______________________________________________________
the machine generate file in every 24 hrs
but our "C Shift" from 23:00 to 7:00 which is combination of two days. how can i identify it is intime of an employee.


>>>>>Please see my Attach DOC file and give me a solution as urself.




Re: Urgent Help for 4 Shift Attendance procedure. [message #284259 is a reply to message #284256] Thu, 29 November 2007 04:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please first read and follow OraFAQ Forum Guide, especially sections about "How to format your post?", about titles, about "urgent", about...

Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: Urgent Help for 4 Shift Attendance procedure. [message #284489 is a reply to message #284256] Thu, 29 November 2007 21:22 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I think the issue for you is you need to go back and rethink this problem a bit more.

I see too many flaws in the code you have provided and it makes it clear to me that someone needs to do some real design. For example, you have not indicated how you intend to handle situations where someone simply "forgets" to clock in or clock out. Nor why your solution expects only a maximum of three periods of time in one day per employee. Seems to me there are too many ways this can break.

Sorry, wish I could do better for you. In then end, without more rules, It is not possible to know if any given entry represents an in or out time.

Good luck, Kevin.
Re: Urgent Help for 4 Shift Attendance procedure. [message #285390 is a reply to message #284256] Tue, 04 December 2007 06:32 Go to previous messageGo to next message
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)

Re: Urgent Help for 4 Shift Attendance procedure. [message #285412 is a reply to message #285390] Tue, 04 December 2007 07:39 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Please read http://www.orafaq.com/forum/t/88153/0/
Previous Topic: Schema level Trigger on DML operations
Next Topic: Help with cleanup query
Goto Forum:
  


Current Time: Wed Dec 07 20:41:34 CST 2016

Total time taken to generate the page: 0.11887 seconds