Hi,
I have two tables
PUNCHDATA |
empid | punchdate | inpunch | outpunch |
1 | 01/01/2014 | 01/01/2014 08:00 | 01/01/2014 12:00 |
1 | 01/01/2014 | 01/01/2014 12:30 | |
1 | 01/01/2014 | 01/01/2014 16:00 | |
1 | 02/01/2014 | 02/01/2014 08:00 | 02/01/2014 12:00 |
1 | 02/01/2014 | 02/01/2014 13:00 | 02/01/2014 16:00 |
ATTSHEET |
empid | attdate | shiftend | unpaids | punchanomaly |
1 | 01/01/2014 | 16:00 | 0 | 1 |
1 | 02/01/2014 | 16:00 | 0 | 0 |
I want to calculate unpaid minutes for employees.
If there is any punching anomaly for an employee for a day, ie. if there is no corresponding in/out punch, then the full day would be unpaid.
So i want to skip the rows till next date for that employee or if it is not there skip rows till next employee data
If there is no anomaly, i want to get next in punch of that employee and calculate the minutes between current out punch and next in punch.
If next in punch is greater than shift end or If there is no next in punch for that day, then minutes between current out punch and shift end need to be calculated.
=========================================
DECLARE CURSOR punch_cur IS
SELECT s.employee_id empid
, s.attdate attdt
, p.inpunch ip
, p.outpunch op
, s.punchanomaly pa
, s.shiftend se
FROM ATTSHEET s , punchdata p
where s.employee_id = p.employee_id
and s.attdate = p.punchdate;
unpaidmins number(4);
BEGIN
FOR punchrec IN punch_cur
LOOP
IF punchrec.pa=1
THEN
update ATTSHEET set unpaids=480 where employee_id=punchrec.empid and attdate=punchrec.attdt;
/*
If punch anomaly for a day is true, then full day ( 8 hours) will be considered as unpaid.
No need to process any punches for that day.
Skip rows till next available date for that employee.
If there is no such rows (May be no more punches of him are to be procesed.) for that employee, then go to next employee data.
So next iteration would skip unwanted rows and point to the desired row.
Is it possible??
*/
ELSE
/*
Get next in punch of that employee and calculate the minutes between the out punch and next in punch.
So without iterating through loop, can we take the value of next row, or any row based on parameters passed?
*/
update ATTSHEET set ATTSHEET.unpaids=ATTSHEET.unpaids+unpaidmins where employee_id=punchrec.empid and attdate=punchrec.attdt;
END IF;
END LOOP;
END;
/
=========================================
Sample Data
| CREATE TABLE PUNCHDATA AS | | |
| | | |
| ( | | |
| | | |
| SELECT 1 employee_id, TO_DATE('01/01/2014', 'DD/MM/YYYY') PUNCHDATE, TO_DATE('01/01/2014 08:00', 'DD/MM/YYYY HH24:MI') INPUNCH, TO_DATE('01/01/2014 12:00' , 'DD/MM/YYYY HH24:MI') OUTPUNCH FROM DUAL UNION ALL | | |
| SELECT 1 employee_id, TO_DATE('01/01/2014', 'DD/MM/YYYY') PUNCHDATE, TO_DATE('01/01/2014 12:30', 'DD/MM/YYYY HH24:MI') INPUNCH, NULL OUTPUNCH FROM DUAL UNION ALL | | |
| SELECT 1 employee_id, TO_DATE('01/01/2014', 'DD/MM/YYYY') PUNCHDATE, TO_DATE('01/01/2014 16:00', 'DD/MM/YYYY HH24:MI') PUNCHIN, NULL PUNCHOUT FROM DUAL UNION ALL | | |
| SELECT 1 employee_id, TO_DATE('02/02/2014', 'DD/MM/YYYY') PUNCHDATE, TO_DATE('02/01/2014 08:00', 'DD/MM/YYYY HH24:MI')PUNCHIN, TO_DATE('02/01/2014 12:00' , 'DD/MM/YYYY HH24:MI') PUNCHOUT FROM DUAL UNION ALL | | |
| SELECT 1 employee_id, TO_DATE('02/02/2014', 'DD/MM/YYYY') PUNCHDATE, TO_DATE('02/01/2014 13:00', 'DD/MM/YYYY HH24:MI')PUNCHIN, TO_DATE('02/01/2014 16:00' , 'DD/MM/YYYY HH24:MI') PUNCHOUT FROM DUAL | | |
| | |
| ); | | |
| |
| | |
| create table ATTSHEET as | | |
| | | |
| ( | | |
| select 1 employee_id,to_date('01/01/2014','DD/MM/YYYY') ATTDATE,'16:00' shiftend,0 UNPAIDS , 1 PUNCHANOMALY from dual union all | | |
| select 1 employee_id,to_date('02/01/2014','DD/MM/YYYY') ATTDATE,'16:00' shiftend,0 UNPAIDS , 0 PUNCHANOMALY from dual | | |
| | |
| ); | | |