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  |
mmohsinaziz
Messages: 79
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 34 times)
|
|
|
|
| Re: Calculate Working Day [message #573660 is a reply to message #573650] |
Wed, 02 January 2013 02:05  |
 |
Michel Cadot
Messages: 54140
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 |
mmohsinaziz
Messages: 79
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 |
|
Michel Cadot
Messages: 54140
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 |
mmohsinaziz
Messages: 79
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 #573699 is a reply to message #573694] |
Wed, 02 January 2013 07:28 |
mmohsinaziz
Messages: 79
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 |
|
Michel Cadot
Messages: 54140
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 |
mmohsinaziz
Messages: 79
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 #573758 is a reply to message #573742] |
Thu, 03 January 2013 01:03 |
mmohsinaziz
Messages: 79
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
|
|
|
|
Goto Forum:
Current Time: Mon May 20 08:21:29 CDT 2013
Total time taken to generate the page: 0.17458 seconds
|
] 
Members
Search
Help
Register
Login
Home
