Home » SQL & PL/SQL » SQL & PL/SQL » Calculate Working Day (Oracle 10G, Windows 2003)
Calculate Working Day [message #573650] Tue, 01 January 2013 23:09 Go to next message
mmohsinaziz
Messages: 85
Registered: May 2012
Member
Dear Experts,
I need a query to find out the working days. I have attached the sample script to create table and data.
Here is the description of the tables.

Emp1 : To record the employe's information and weekly rest day.
Attendance :- To record daily attendance.
Leave_appovd : To record the approved leaves.
Holiday : To record the holidays.

W = Attendance
R = Weekly Rest
L = Leave
H = Holiday
A = Absent

Required output is
Emp No.	In Date	        Out Date       Type
7369	12/1/2012	12/1/2012	W
7369	12/2/2012	12/2/2012	R
7369	12/3/2012	12/3/2012	W
7369	12/4/2012	12/4/2012	W
7369	12/5/2012	12/5/2012	W
7369	12/6/2012	12/6/2012	L
7369	12/7/2012	12/7/2012	H
7369	12/8/2012	12/8/2012	H
7369	12/9/2012	12/9/2012	R
7369	12/10/2012	12/10/2012	A
7369	12/11/2012	12/11/2012	W
7369	12/12/2012	12/12/2012	W
7369	12/13/2012	12/13/2012	W
7369	12/14/2012	12/14/2012	W
7369	12/15/2012	12/15/2012	L
7369	12/16/2012	12/16/2012	L
7369	12/17/2012	12/17/2012	L
7369	12/18/2012	12/18/2012	W
7369	12/19/2012	12/19/2012	W
7369	12/20/2012	12/20/2012	W
7369	12/21/2012	12/21/2012	W
7369	12/22/2012	12/22/2012	W
7369	12/23/2012	12/23/2012	R
7369	12/24/2012	12/24/2012	A
7369	12/25/2012	12/25/2012	A
7369	12/26/2012	12/26/2012	A
7369	12/27/2012	12/27/2012	W
7369	12/28/2012	12/28/2012	W
7369	12/29/2012	12/29/2012	W
7369	12/30/2012	12/30/2012	R
7369	12/31/2012	12/31/2012	W

7782	12/1/2012	12/1/2012	W
7782	12/2/2012	12/2/2012	W
7782	12/3/2012	12/3/2012	W
7782	12/4/2012	12/4/2012	W
7782	12/5/2012	12/5/2012	W
7782	12/6/2012	12/6/2012	W


Seperate Query will be run for the following output.

	                 Woking Days						
Empno	Attend 	WeeklyRests	Leaves	Holidays Total	Absents	G. Total
7369	  17	  4	            4	   2	   27	  4         31
7782      6       0                 0      0       6      25        31



If any body work on weekly rest or holiday, it will be considered as weekly rest and holiday. There working on these days will be treated seperatly.
  • Attachment: attend.sql
    (Size: 6.90KB, Downloaded 68 times)
Re: Calculate Working Day [message #573660 is a reply to message #573650] Wed, 02 January 2013 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 58521
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Assuming that if someone is absent on weekly rest or holiday day, he actually is in weekly rest or holiday (and not absent as your result shows):
SQL> break on "Emp no." dup skip 1
SQL> with 
  2    cal as (
  3      select to_date('01/12/2012','DD/MM/YYYY')+level-1 d from dual connect by level <= 31
  4    )
  5  select e.empno "Emp no.", c.d "In Date", c.d+1 "Out Date", 
  6         case
  7           when l.empno is not null then 'L'
  8           when to_char(c.d, 'DY') = e.wrest then 'R'
  9           when h.sdate is not null then 'H'
 10           when a.empno is not null then 'W'
 11           else 'A'
 12         end "Type"
 13  from ( cal c cross join emp1 e ) 
 14       left outer join holidays h 
 15         on c.d between h.sdate and h.edate
 16       left outer join attendance a 
 17         on a.empno = e.empno and c.d between a.indate and a.outdate
 18       left outer join leave_approvd l 
 19         on l.empno = e.empno and c.d between l.start_date and l.end_date
 20  order by e.empno, c.d     
 21  /
   Emp no. In Date     Out Date    T
---------- ----------- ----------- -
      7369 01-DEC-2012 02-DEC-2012 W
      7369 02-DEC-2012 03-DEC-2012 R
      7369 03-DEC-2012 04-DEC-2012 W
      7369 04-DEC-2012 05-DEC-2012 W
      7369 05-DEC-2012 06-DEC-2012 W
      7369 06-DEC-2012 07-DEC-2012 L
      7369 07-DEC-2012 08-DEC-2012 H
      7369 08-DEC-2012 09-DEC-2012 H
      7369 09-DEC-2012 10-DEC-2012 R
      7369 10-DEC-2012 11-DEC-2012 A
      7369 11-DEC-2012 12-DEC-2012 W
      7369 12-DEC-2012 13-DEC-2012 W
      7369 13-DEC-2012 14-DEC-2012 W
      7369 14-DEC-2012 15-DEC-2012 W
      7369 15-DEC-2012 16-DEC-2012 L
      7369 16-DEC-2012 17-DEC-2012 L
      7369 17-DEC-2012 18-DEC-2012 L
      7369 18-DEC-2012 19-DEC-2012 W
      7369 19-DEC-2012 20-DEC-2012 W
      7369 20-DEC-2012 21-DEC-2012 W
      7369 21-DEC-2012 22-DEC-2012 W
      7369 22-DEC-2012 23-DEC-2012 W
      7369 23-DEC-2012 24-DEC-2012 R
      7369 24-DEC-2012 25-DEC-2012 A
      7369 25-DEC-2012 26-DEC-2012 H
      7369 26-DEC-2012 27-DEC-2012 A
      7369 27-DEC-2012 28-DEC-2012 W
      7369 28-DEC-2012 29-DEC-2012 W
      7369 29-DEC-2012 30-DEC-2012 W
      7369 30-DEC-2012 31-DEC-2012 R
      7369 31-DEC-2012 01-JAN-2013 W

      7782 01-DEC-2012 02-DEC-2012 W
      7782 02-DEC-2012 03-DEC-2012 W
      7782 03-DEC-2012 04-DEC-2012 W
      7782 04-DEC-2012 05-DEC-2012 W
      7782 05-DEC-2012 06-DEC-2012 W
      7782 06-DEC-2012 07-DEC-2012 W
      7782 07-DEC-2012 08-DEC-2012 R
      7782 08-DEC-2012 09-DEC-2012 H
      7782 09-DEC-2012 10-DEC-2012 A
      7782 10-DEC-2012 11-DEC-2012 A
      7782 11-DEC-2012 12-DEC-2012 A
      7782 12-DEC-2012 13-DEC-2012 A
      7782 13-DEC-2012 14-DEC-2012 A
      7782 14-DEC-2012 15-DEC-2012 R
      7782 15-DEC-2012 16-DEC-2012 A
      7782 16-DEC-2012 17-DEC-2012 A
      7782 17-DEC-2012 18-DEC-2012 A
      7782 18-DEC-2012 19-DEC-2012 A
      7782 19-DEC-2012 20-DEC-2012 A
      7782 20-DEC-2012 21-DEC-2012 A
      7782 21-DEC-2012 22-DEC-2012 R
      7782 22-DEC-2012 23-DEC-2012 A
      7782 23-DEC-2012 24-DEC-2012 A
      7782 24-DEC-2012 25-DEC-2012 A
      7782 25-DEC-2012 26-DEC-2012 H
      7782 26-DEC-2012 27-DEC-2012 A
      7782 27-DEC-2012 28-DEC-2012 A
      7782 28-DEC-2012 29-DEC-2012 R
      7782 29-DEC-2012 30-DEC-2012 A
      7782 30-DEC-2012 31-DEC-2012 A
      7782 31-DEC-2012 01-JAN-2013 A

The other query is just a pivot on the first one:
SQL> clear break 
breaks cleared
SQL> with 
  2    cal as (
  3      select to_date('01/12/2012','DD/MM/YYYY')+level-1 d from dual connect by level <= 31
  4    ),
  5    days as (
  6      select e.empno, c.d id, c.d+1 od, 
  7             case
  8               when l.empno is not null then 'L'
  9               when to_char(c.d, 'DY') = e.wrest then 'R'
 10               when h.sdate is not null then 'H'
 11               when a.empno is not null then 'W'
 12               else 'A'
 13             end type
 14      from ( cal c cross join emp1 e ) 
 15           left outer join holidays h 
 16             on c.d between h.sdate and h.edate
 17           left outer join attendance a 
 18             on a.empno = e.empno and c.d between a.indate and a.outdate
 19           left outer join leave_approvd l 
 20             on l.empno = e.empno and c.d between l.start_date and l.end_date
 21      )
 22  select d.empno "Empno", 
 23         count(decode(type, 'W', 1)) "Attend",
 24         count(decode(type, 'R', 1)) "WeeklyRests",
 25         count(decode(type, 'L', 1)) "Leaves",
 26         count(decode(type, 'H', 1)) "Holidays",
 27         count(nullif(type, 'A'))    "Total",
 28         count(decode(type, 'A', 1)) "Absents",
 29         count(*)                    "G. Total"
 30  from days d
 31  group by d.empno
 32  order by d.empno
 33  /
     Empno     Attend WeeklyRests     Leaves   Holidays      Total    Absents   G. Total
---------- ---------- ----------- ---------- ---------- ---------- ---------- ----------
      7369         17           4          4          3         28          3         31
      7782          6           4          0          2         12         19         31

Regards
Michel

[Updated on: Wed, 02 January 2013 02:06]

Report message to a moderator

Re: Calculate Working Day [message #573666 is a reply to message #573660] Wed, 02 January 2013 03:31 Go to previous messageGo to next message
mmohsinaziz
Messages: 85
Registered: May 2012
Member
Dear Michel,
Thanks for your reply.
For Empno 7369
25-dec-12 is actually a holiday but it is marked as absent because of sandwitch absents (Absent on 24-dec-12 and 26-dec-12).

For Empno 7782
As he is not comming after 6-dec-12 hence rest of the month will be considered as absent. Salary will be paid for 06 days only.

Please modify the query to accomodate these.

Thanks
M. Mohsin
Re: Calculate Working Day [message #573668 is a reply to message #573666] Wed, 02 January 2013 03:38 Go to previous messageGo to next message
Michel Cadot
Messages: 58521
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please modify the query to accomodate these.


Yes, I could but FIRST try to do it by yourself and come back with your solution if you find it or your tries if you can't.

Hint: Have a look at LAG/LEAD functions.

Regards
Michel

[Updated on: Wed, 02 January 2013 03:39]

Report message to a moderator

Re: Calculate Working Day [message #573689 is a reply to message #573668] Wed, 02 January 2013 06:02 Go to previous messageGo to next message
mmohsinaziz
Messages: 85
Registered: May 2012
Member
Dear Michal,
I have tried to get the required result with following query but failed.

with 
      cal as (
        select to_date('01/12/2012','DD/MM/YYYY')+level-1 d from dual connect by level <= 31
      ),
	days as (
    select e.empno , c.d id, c.d od, 
           case
             when l.empno is not null then 'L'
             when to_char(c.d, 'DY') = e.wrest then 'R'
             when h.sdate is not null then 'H'
            when a.empno is not null then 'W'
            else 'A'
          end Type
   from ( cal c cross join emp1 e ) 
        left outer join attendance a 
          on a.empno = e.empno and c.d between a.indate and a.outdate
        left outer join holidays h 
          on c.d between h.sdate and h.edate
        left outer join leave_approvd l 
          on l.empno = e.empno and c.d between l.start_date and l.end_date
	)
select d.empno, d.id, d.od,d.type,
lag(d.type) over (order by d.empno,d.id) gtype,
lead(d.type) over (order by d.empno,d.id) dtype,
decode(d.type,lag(d.type) over (order by d.empno,d.id),lead(d.type) over (order by d.empno,d.id))ttype
from days d
   order by d.empno,d.id


Please help
Re: Calculate Working Day [message #573694 is a reply to message #573689] Wed, 02 January 2013 06:36 Go to previous messageGo to next message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
Failed how?
Re: Calculate Working Day [message #573699 is a reply to message #573694] Wed, 02 January 2013 07:28 Go to previous messageGo to next message
mmohsinaziz
Messages: 85
Registered: May 2012
Member
It is not giving the required result.
Required Result is
Emp No.	In Date	        Out Date       Type
7369	12/1/2012	12/1/2012	W
7369	12/2/2012	12/2/2012	R
7369	12/3/2012	12/3/2012	W
7369	12/4/2012	12/4/2012	W
7369	12/5/2012	12/5/2012	W
7369	12/6/2012	12/6/2012	L
7369	12/7/2012	12/7/2012	H
7369	12/8/2012	12/8/2012	H
7369	12/9/2012	12/9/2012	R
7369	12/10/2012	12/10/2012	A
7369	12/11/2012	12/11/2012	W
7369	12/12/2012	12/12/2012	W
7369	12/13/2012	12/13/2012	W
7369	12/14/2012	12/14/2012	W
7369	12/15/2012	12/15/2012	L
7369	12/16/2012	12/16/2012	L
7369	12/17/2012	12/17/2012	L
7369	12/18/2012	12/18/2012	W
7369	12/19/2012	12/19/2012	W
7369	12/20/2012	12/20/2012	W
7369	12/21/2012	12/21/2012	W
7369	12/22/2012	12/22/2012	W
7369	12/23/2012	12/23/2012	R
7369	12/24/2012	12/24/2012	A
7369	12/25/2012	12/25/2012	A
7369	12/26/2012	12/26/2012	A
7369	12/27/2012	12/27/2012	W
7369	12/28/2012	12/28/2012	W
7369	12/29/2012	12/29/2012	W
7369	12/30/2012	12/30/2012	R
7369	12/31/2012	12/31/2012	W

7782	12/1/2012	12/1/2012	W
7782	12/2/2012	12/2/2012	W
7782	12/3/2012	12/3/2012	W
7782	12/4/2012	12/4/2012	W
7782	12/5/2012	12/5/2012	W
7782	12/6/2012	12/6/2012	W


But with query modified by me is
     EMPNO ID        OD        T G D T
---------- --------- --------- - - - -
      7369 01-DEC-12 01-DEC-12 W   R
      7369 02-DEC-12 02-DEC-12 R W W
      7369 03-DEC-12 03-DEC-12 W R W
      7369 04-DEC-12 04-DEC-12 W W W W
      7369 05-DEC-12 05-DEC-12 W W L L
      7369 06-DEC-12 06-DEC-12 L W H
      7369 07-DEC-12 07-DEC-12 H L H
      7369 08-DEC-12 08-DEC-12 H H R R
      7369 09-DEC-12 09-DEC-12 R H A
      7369 10-DEC-12 10-DEC-12 A R W
      7369 11-DEC-12 11-DEC-12 W A W
      7369 12-DEC-12 12-DEC-12 W W W W
      7369 13-DEC-12 13-DEC-12 W W W W
      7369 14-DEC-12 14-DEC-12 W W L L
      7369 15-DEC-12 15-DEC-12 L W L
      7369 16-DEC-12 16-DEC-12 L L L L
      7369 17-DEC-12 17-DEC-12 L L W W
      7369 18-DEC-12 18-DEC-12 W L W
      7369 19-DEC-12 19-DEC-12 W W W W
      7369 20-DEC-12 20-DEC-12 W W W W
      7369 21-DEC-12 21-DEC-12 W W W W
      7369 22-DEC-12 22-DEC-12 W W R R
      7369 23-DEC-12 23-DEC-12 R W A
      7369 24-DEC-12 24-DEC-12 A R H
      7369 25-DEC-12 25-DEC-12 H A A
      7369 26-DEC-12 26-DEC-12 A H W
      7369 27-DEC-12 27-DEC-12 W A W
      7369 28-DEC-12 28-DEC-12 W W W W
      7369 29-DEC-12 29-DEC-12 W W R R
      7369 30-DEC-12 30-DEC-12 R W W
      7369 31-DEC-12 31-DEC-12 W R W

      7782 01-DEC-12 01-DEC-12 W W W W
      7782 02-DEC-12 02-DEC-12 W W W W
      7782 03-DEC-12 03-DEC-12 W W W W
      7782 04-DEC-12 04-DEC-12 W W W W
      7782 05-DEC-12 05-DEC-12 W W W W
      7782 06-DEC-12 06-DEC-12 W W R R
      7782 07-DEC-12 07-DEC-12 R W H
      7782 08-DEC-12 08-DEC-12 H R A
      7782 09-DEC-12 09-DEC-12 A H A
      7782 10-DEC-12 10-DEC-12 A A A A
      7782 11-DEC-12 11-DEC-12 A A A A
      7782 12-DEC-12 12-DEC-12 A A A A
      7782 13-DEC-12 13-DEC-12 A A R R
      7782 14-DEC-12 14-DEC-12 R A A
      7782 15-DEC-12 15-DEC-12 A R A
      7782 16-DEC-12 16-DEC-12 A A A A
      7782 17-DEC-12 17-DEC-12 A A A A
      7782 18-DEC-12 18-DEC-12 A A A A
      7782 19-DEC-12 19-DEC-12 A A A A
      7782 20-DEC-12 20-DEC-12 A A R R
      7782 21-DEC-12 21-DEC-12 R A A
      7782 22-DEC-12 22-DEC-12 A R A
      7782 23-DEC-12 23-DEC-12 A A A A
      7782 24-DEC-12 24-DEC-12 A A H H
      7782 25-DEC-12 25-DEC-12 H A A
      7782 26-DEC-12 26-DEC-12 A H A
      7782 27-DEC-12 27-DEC-12 A A R R
      7782 28-DEC-12 28-DEC-12 R A A
      7782 29-DEC-12 29-DEC-12 A R A
      7782 30-DEC-12 30-DEC-12 A A A A
      7782 31-DEC-12 31-DEC-12 A A


I am trying to use lag and lead to replace type column data with 'A' if previous date type is 'A' and next date type 'A' then in between what ever the type is, it must be 'A'.
Re: Calculate Working Day [message #573708 is a reply to message #573699] Wed, 02 January 2013 10:04 Go to previous messageGo to next message
Michel Cadot
Messages: 58521
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am trying to use lag and lead to replace type column data with 'A' if previous date type is 'A' and next date type 'A' then in between what ever the type is, it must be 'A'.


So you have the lag and lead values, so what is the problem? Just use CASE.

Also you have to answer the question, if you have 1 A then 10 H then 1 A, does the 10 H turn to 10 A?
If yes, then you have not to just check the previous and next values.

In addition, I don't think "what ever the type is" is correct, I don't think someone will agree if he works a day between 2 absences to see his working day changed into an absence, won't you?

Regards
Michel

[Updated on: Wed, 02 January 2013 10:08]

Report message to a moderator

Re: Calculate Working Day [message #573739 is a reply to message #573708] Wed, 02 January 2013 22:51 Go to previous messageGo to next message
mmohsinaziz
Messages: 85
Registered: May 2012
Member
Dear Michel,
Yes, You are right. If there is holiday or weekly rest between 2 absences and he does not work then it will be considered as absent.

I will try to solve it using case statement and post the query.

Thanks
M. Mohsin
Re: Calculate Working Day [message #573742 is a reply to message #573739] Wed, 02 January 2013 23:02 Go to previous messageGo to next message
mmohsinaziz
Messages: 85
Registered: May 2012
Member
Dear Michel,
Yes, if one has 1A then 10H then 1A, 10H will be turn to 10A.

Regards
M. Mohsin
Re: Calculate Working Day [message #573758 is a reply to message #573742] Thu, 03 January 2013 01:03 Go to previous message
mmohsinaziz
Messages: 85
Registered: May 2012
Member
Dear Michel,
Here is query that gives the result closer to my requirement but it may not fullfill the points you raised and are very valid poits.

WITH cal AS
     (SELECT     TO_DATE ('01/12/2012', 'DD/MM/YYYY') + LEVEL - 1 d
            FROM DUAL
      CONNECT BY LEVEL <= 31),
     days AS
     (SELECT e.empno, c.d ID, c.d od,
             CASE
                WHEN l.empno IS NOT NULL
                   THEN 'L'
                WHEN TO_CHAR (c.d, 'DY') = e.wrest
                   THEN 'R'
                WHEN h.sdate IS NOT NULL
                   THEN 'H'
                WHEN a.empno IS NOT NULL
                   THEN 'W'
                ELSE 'A'
             END TYPE
        FROM (cal c CROSS JOIN emp1 e)
             LEFT OUTER JOIN
             attendance a
             ON a.empno = e.empno AND c.d BETWEEN a.indate AND a.outdate
             LEFT OUTER JOIN holidays h ON c.d BETWEEN h.sdate AND h.edate
             LEFT OUTER JOIN leave_approvd l
             ON l.empno = e.empno AND c.d BETWEEN l.start_date AND l.end_date
             )
SELECT   d.empno, d.ID "In Date", d.od "Out Date", 
 
         CASE
            WHEN LAG (d.TYPE) OVER (ORDER BY d.empno, d.ID) = 'A'
               THEN CASE
                      WHEN LEAD (d.TYPE) OVER (ORDER BY d.empno, d.ID) =
                                                                   'A'
                         THEN 'A'
                      ELSE d.TYPE
                   END
            ELSE d.TYPE
         END "Type"
    FROM days d
ORDER BY d.empno, d.ID
/


Output is
     EMPNO In Date   Out Date  T
---------- --------- --------- -
      7369 01-DEC-12 01-DEC-12 W
      7369 02-DEC-12 02-DEC-12 R
      7369 03-DEC-12 03-DEC-12 W
      7369 04-DEC-12 04-DEC-12 W
      7369 05-DEC-12 05-DEC-12 W
      7369 06-DEC-12 06-DEC-12 L
      7369 07-DEC-12 07-DEC-12 H
      7369 08-DEC-12 08-DEC-12 H
      7369 09-DEC-12 09-DEC-12 R
      7369 10-DEC-12 10-DEC-12 A
      7369 11-DEC-12 11-DEC-12 W
      7369 12-DEC-12 12-DEC-12 W
      7369 13-DEC-12 13-DEC-12 W
      7369 14-DEC-12 14-DEC-12 W
      7369 15-DEC-12 15-DEC-12 L
      7369 16-DEC-12 16-DEC-12 L
      7369 17-DEC-12 17-DEC-12 L
      7369 18-DEC-12 18-DEC-12 W
      7369 19-DEC-12 19-DEC-12 W
      7369 20-DEC-12 20-DEC-12 W
      7369 21-DEC-12 21-DEC-12 W
      7369 22-DEC-12 22-DEC-12 W
      7369 23-DEC-12 23-DEC-12 R
      7369 24-DEC-12 24-DEC-12 A
      7369 25-DEC-12 25-DEC-12 A
      7369 26-DEC-12 26-DEC-12 A
      7369 27-DEC-12 27-DEC-12 W
      7369 28-DEC-12 28-DEC-12 W
      7369 29-DEC-12 29-DEC-12 W
      7369 30-DEC-12 30-DEC-12 R
      7369 31-DEC-12 31-DEC-12 W

      7782 01-DEC-12 01-DEC-12 W
      7782 02-DEC-12 02-DEC-12 W
      7782 03-DEC-12 03-DEC-12 W
      7782 04-DEC-12 04-DEC-12 W
      7782 05-DEC-12 05-DEC-12 W
      7782 06-DEC-12 06-DEC-12 W
      7782 07-DEC-12 07-DEC-12 R
      7782 08-DEC-12 08-DEC-12 H
      7782 09-DEC-12 09-DEC-12 A
      7782 10-DEC-12 10-DEC-12 A
      7782 11-DEC-12 11-DEC-12 A
      7782 12-DEC-12 12-DEC-12 A
      7782 13-DEC-12 13-DEC-12 A
      7782 14-DEC-12 14-DEC-12 A
      7782 15-DEC-12 15-DEC-12 A
      7782 16-DEC-12 16-DEC-12 A
      7782 17-DEC-12 17-DEC-12 A
      7782 18-DEC-12 18-DEC-12 A
      7782 19-DEC-12 19-DEC-12 A
      7782 20-DEC-12 20-DEC-12 A
      7782 21-DEC-12 21-DEC-12 A
      7782 22-DEC-12 22-DEC-12 A
      7782 23-DEC-12 23-DEC-12 A
      7782 24-DEC-12 24-DEC-12 A
      7782 25-DEC-12 25-DEC-12 A
      7782 26-DEC-12 26-DEC-12 A
      7782 27-DEC-12 27-DEC-12 A
      7782 28-DEC-12 28-DEC-12 A
      7782 29-DEC-12 29-DEC-12 A
      7782 30-DEC-12 30-DEC-12 A
      7782 31-DEC-12 31-DEC-12 A

62 rows selected.

Please help how to accomodate the followings.
1) If one has 1 A then 10 H then 1 A, it turn 10 H to 10 A
2) If there is holiday or weekly rest between 2 consacutive absences and he does not work then only it will be considered as absent.
3) For Empno 7782, it must be 'A' after 06-dec-12 but it getting 07-dec-12 as 'R' and '08-dec-12' as 'H'.

Regards
M. Mohsin
Previous Topic: Update collections using TABLE operator
Next Topic: how to insert data multiple bases using the multiple database links?
Goto Forum:
  


Current Time: Wed Jul 23 17:31:23 CDT 2014

Total time taken to generate the page: 0.08963 seconds