Home » SQL & PL/SQL » SQL & PL/SQL » Extract Absent days from the month (Oracle 10g)
Extract Absent days from the month [message #573476] Sat, 29 December 2012 06:16 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Dear all, i need to find the number of days employee was absent for the month, we are maintaining a table of attendance , where the daily data of hours is captured, what i need now is to capture total number of days he was absent or not present.How can i get the sum of absent days per employee based on given month, as the difficulty is i have to consider 30 days as standard working days for the month , regardless of months having either 30 days (April,June,September,November),or 31(January,March,May,July,August,December) or 28(Consider leap year also february) days.For eg ,If he is absent for 31 days in december then it should consider only 30 days as absent or if he is absent for 28 days in february still it should consider 30 Days as absent.



create table ot_job_det (jt_txn varchar2(12),jt_no number,jt_dt date ,jt_emp_code varchar2(6),jt_duration number )

delete from ot_job_det

insert into ot_job_det values ( 'time',1,'04-dec-2012','AA',10);

insert into ot_job_det values ('time',2,'01-dec-2012','AA',12);

insert into ot_job_det values ('time',3,'09-dec-2012','BB',7);

insert into ot_job_det values ('time',4,'14-dec-2012','CC',2.43);

insert into ot_job_det values ('time',5,'15-dec-2012','DD',2);

--desired output i want is -- I will be passing the year and month as parameter.

jt_emp_code  no_of_days_absent
AA             28                --He was present on 1st and 4th december so 30-2 = 28
BB             29            
CC             29
DD             29

I managed to do this with the given query , but this will check only for 31 days 
and 30 how to consider the february month or any leap year.

SELECT JT_EMP_CODE,DECODE(SIGN(30-COUNT(JT_DT)),-1,30,30-COUNT(JT_DT)) a FROM
OT_JOB_DET WHERE 
TO_CHAR(JT_DT,'YYYYMM')='201212'
GROUP BY JT_EMP_CODE;



[Updated on: Sat, 29 December 2012 09:09] by Moderator

Report message to a moderator

Re: Extract Absent days from the month [message #573478 is a reply to message #573476] Sat, 29 December 2012 07:08 Go to previous messageGo to next message
Littlefoot
Messages: 19334
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
CASE (instead of DECODE) is simpler to write and maintain when complex options should be discussed. So, maybe you could give it a try.
Re: Extract Absent days from the month [message #573480 is a reply to message #573476] Sat, 29 December 2012 07:31 Go to previous messageGo to next message
flyboy
Messages: 1769
Registered: November 2006
Senior Member
Alternatively, LAST_DAY returns date of the last day of the month, so (as month always starts with 1st) you may simply use day extracted from it as number of days in that month:
select extract( day from last_day(jt_dt) ) from ot_job_det;

By the way, your test case is not representative at all.
Re: Extract Absent days from the month [message #573487 is a reply to message #573480] Sat, 29 December 2012 11:05 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks littlefoot and flyboy , please find below i have the test case presented again.


create table ot_job_det (jt_txn varchar2(12),jt_no number,jt_dt date ,
jt_emp_code varchar2(6),jt_duration number );


insert into ot_job_det values ( 'time',1,'04-dec-2012','AA',10);

insert into ot_job_det values ('time',2,'01-dec-2012','AA',12);

insert into ot_job_det values ('time',3,'09-dec-2012','BB',7);

insert into ot_job_det values ('time',4,'14-dec-2012','CC',2.43);

insert into ot_job_det values ('time',5,'15-dec-2012','DD',2);

--new inserts  for february

insert into ot_job_det values ('time',4,'14-FEB-2012','CC',2.43);

insert into ot_job_det values ('time',5,'15-FEB-2012','DD',2);




--desired output i want is below , depending upon the current month, i have found a 
way to tackle 31 days but what about february month which usually has 28 days.

SELECT JT_EMP_CODE,DECODE(SIGN(30-COUNT(JT_DT)),-1,30,30-COUNT(JT_DT)) no_of_days_absent FROM
OT_JOB_DET WHERE 
TO_CHAR(JT_DT,'YYYYMM')='201212'
GROUP BY JT_EMP_CODE;

jt_emp_code  no_of_days_absent
AA             28                --He was present on 1st and 4th december so 30-2 = 28
BB             29            
CC             29
DD             29



--new inserts  for february

insert into ot_job_det values ('time',4,'14-FEB-2012','CC',2.43);

insert into ot_job_det values ('time',5,'15-FEB-2012','DD',2);


SELECT JT_EMP_CODE,DECODE(SIGN(30-COUNT(JT_DT)),-1,30,30-COUNT(JT_DT)) no_of_days_absent FROM
OT_JOB_DET WHERE 
TO_CHAR(JT_DT,'YYYYMM')='201202'
GROUP BY JT_EMP_CODE;

--Desired output or result wanted for february is,since this year february had 29, 
still it should consider it as 30 days for the month and bring the output as below 
for the february month.

jt_emp_code  no_of_days_absent
CC             29
DD             29


[Updated on: Sat, 29 December 2012 11:34] by Moderator

Report message to a moderator

Re: Extract Absent days from the month [message #573489 is a reply to message #573487] Sat, 29 December 2012 11:35 Go to previous messageGo to next message
Michel Cadot
Messages: 58616
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again code tags are for CODE and chart NOT for text.

Regards
Michel
Re: Extract Absent days from the month [message #573490 is a reply to message #573476] Sat, 29 December 2012 11:37 Go to previous messageGo to next message
flyboy
Messages: 1769
Registered: November 2006
Senior Member
Your query does give the desired result, so I do not understand what problem are you trying to report here.

Although, the logic is quite strange - on February employee will be absent 1 or 2 days, even if he attended all days.
I also do not understand, why you compute absence as 30 days when employee attended all days of 31day month (e.g. on December) - just examine your DECODE expression.

To avoid negative figures, I would probably use
greatest( 30 - count(distinct jt_dt), 0 )

(not sure what shall be reported when there are two or more attendances in one day)

It is really impossible to propose anything without exact requirements though.
Re: Extract Absent days from the month [message #573491 is a reply to message #573490] Sat, 29 December 2012 11:58 Go to previous messageGo to next message
Littlefoot
Messages: 19334
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's the CASE I was talking about. I suppose it returns the result you posted previously, but - as people already said - your "requirement" is, actually, a mystery.
select
  o.jt_emp_code,
  to_char(o.jt_dt, 'mm.yyyy') mm_yyyy,
  30 - case when count(*) = to_number(to_char(last_day(o.jt_dt), 'dd')) then 30
            else count(*)
       end cnt 
from ot_job_det o
group by   
  to_char(o.jt_dt, 'mm.yyyy'),
  o.jt_emp_code,
  last_day(o.jt_dt)
order by   
  to_char(o.jt_dt, 'mm.yyyy'),
  o.jt_emp_code
Re: Extract Absent days from the month [message #573493 is a reply to message #573490] Sat, 29 December 2012 12:34 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

requirement is simple not a mystery for you guys as you all experts.look in case of employee CC who was present only for one day in february on 14th and rest of the days he was absent and i just wanted to show number of days he was absent , as you all know this year february had 29days, so even if it had 29 days , it should treat it as 30 days, that is 30days - no of days present = 29 days. if a month of 30 comes then as its no problem, but if its like december then it has to be 30-no.of.days absent.
Re: Extract Absent days from the month [message #573495 is a reply to message #573493] Sat, 29 December 2012 13:05 Go to previous messageGo to next message
flyboy
Messages: 1769
Registered: November 2006
Senior Member
If your requirements are so simple, I wonder why you do not directly answer all doubts we stated.

We also provided some sample expressions - have you tried any of them? If so, what was the problem? What is problem with your initial query?

Quote:
it should treat it as 30 days, that is 30days - no of days present = 29 days. if a month of 30 comes then as its no problem,
but if its like december then it has to be 30-no.of.days absent.

Using word "like" is not clear at all - which months are they? Also are you sure that the formula will differ for them (30-present vs. 30-absent days)?
Re: Extract Absent days from the month [message #573496 is a reply to message #573491] Sat, 29 December 2012 13:08 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks littlefoot and flyboy for the help as this is the query i was looking for but things are getting little complicated now, i need to add two conditions to the query as follows.
If the employee has total hours of 8 per day then it will be treated as not absent , else if he works for less than 8 hours lets say only for 2 hours then , no.of hours absent will be recorded as 6 hours on that day. i will explain through test inserts.

The first two inserts are marked as full day present for employee AA for december month,and since he was absent for 2 hours on 3rd december his working hours will be recorded as 6 hours on 3rd december and total days absent for him in december will be 28 days and 2 hours.Whereas for bb it will 29.2 absent days/hours and for CC it will be 29.6 and for DD it will be 29.5.


insert into ot_job_det values ( 'time',1,'04-dec-2012','AA',10);

insert into ot_job_det values ('time',2,'01-dec-2012','AA',12);

insert into ot_job_det values ('time',7,'03-dec-2012','AA',6);

insert into ot_job_det values ('time',3,'09-dec-2012','BB',7);

insert into ot_job_det values ('time',4,'14-dec-2012','CC',2);

insert into ot_job_det values ('time',5,'15-dec-2012','DD',3);

[Updated on: Sat, 29 December 2012 13:10]

Report message to a moderator

Re: Extract Absent days from the month [message #573498 is a reply to message #573496] Sat, 29 December 2012 13:23 Go to previous messageGo to next message
BlackSwan
Messages: 22526
Registered: January 2009
Senior Member
what is done for folks who start at 11PM & work until 7AM?

what is done for folks who start at 11PM & work until 7AM where they start in one month & end shift in the next month?
Re: Extract Absent days from the month [message #573499 is a reply to message #573496] Sat, 29 December 2012 13:23 Go to previous messageGo to next message
Littlefoot
Messages: 19334
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Pardon me, but the whole story is just stupid. A month has as many days as it does, whether it is 28, 29, 30 or 31. If I were one of your employees, I wouldn't come to work on any of 31rd in a month because it doesn't matter whether I am there or not.

It is OK to record days and hours when employee was working (for example, the first record: employee AA worked 10 hours on 04 Dec 2012). What do you want to do with his extra 2 hours? He was supposed to stay at work for 8 hours, but he worked 10 hours. So? Will you calculate that as a "bonus"? Will those 2 hours compensate 2 missing hours on 03 Dec 2012 (when that employee stayed at work for 6 hours)? Because, he really spent 16 hours at work in those two days.

So, wouldn't it be fair to calculate exact figures? You have everything you need, why all those strange rules? For example, find number of days in a month (LAST_DAY is perfectly capable of doing it) and multiply that number with 8 (minimum number of hours someone has to be at work). Then sum hours (the last column in a table) and compare it with the number of expected hours (i.e. number of days * 8). Now you know whether some hours/days are missing or not.

However, you told us nothing about weekends, holidays, someone being ill and stuff. Or will that become "a little complicated" tomorrow or next week?

[EDIT: disabled smilies]

[Updated on: Sat, 29 December 2012 13:24]

Report message to a moderator

Re: Extract Absent days from the month [message #573510 is a reply to message #573499] Sat, 29 December 2012 21:51 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks littlefoot well, i will consider the last_day option and then multiply with 8 to get the total number of hours per day,then per week and then per month, well i am not concerned about the extra working hours like 12 since we are taking the report only for absentism, whether he has attended the full 8 hrs to complete a day or else if partial means only those hours will be paid.There is an adjustment entries done for illness and for other holidays there is one table holiday master where the days of holidays are recorded.
Re: Extract Absent days from the month [message #573511 is a reply to message #573495] Sat, 29 December 2012 21:54 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks flyboy, i am using the last_day option to check the number of days in the month and getting exactly what i want, but the problem is extracting of partial hours and that i working on as suggested by littlefoot.
Re: Extract Absent days from the month [message #573512 is a reply to message #573491] Sat, 29 December 2012 22:53 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

When i executed your query with case it shows negative values, may be i need to use greatest.and we need to include fridays as present.
Re: Extract Absent days from the month [message #573515 is a reply to message #573512] Sun, 30 December 2012 01:10 Go to previous messageGo to next message
flyboy
Messages: 1769
Registered: November 2006
Senior Member
arif_md2009 wrote on Sun, 30 December 2012 05:53
When i executed your query with case it shows negative values, may be i need to use greatest.

I see only one reason for getting negative values in Littlefoot's (you replied to his post, so I suppose it is the query you are talking about) expression: multiple entries for one employee in one day.
I already gave expression for counting one day only once; here is improved one for the case when JT_DT contains time part:
count( distinct trunc( jt_dt, 'DD' ) )

arif_md2009 wrote on Sun, 30 December 2012 05:53
and we need to include fridays as present.

What does "include" mean for your test case? There are no Fridays, Saturdays, ... in the output.
I have to admit that I do not understand your "simple" requirement at all: either you described the output of your query in the beginning (so your posts did not contain any requirement at all) or it significantly changed.
Just curious: do (at least) you know what shall the query produce?
Re: Extract Absent days from the month [message #573517 is a reply to message #573515] Sun, 30 December 2012 02:18 Go to previous message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks flyboy and littlefoot for the great help , i used the greatest function to avoid negatives its working fine , yes you are true that i didnt specify the requirement clearly in my first post itself.Apologies for that.Actually i wanted to get the number of absent days for each employee for the month and regardless of number of days every month , our company treats pay days as 30 days only, and if employee works for 31 days also he will be paid for 30 days and if febuary has 29 days also he will be paid based on 30 days.We are maintaining a seperate table called pm_calendar_date where the details of weekend which is friday and holidays are stored along with date , hence the full scenario again.


DROP TABLE OT_JOB_DET

create table ot_job_det (jt_txn varchar2(12),jt_no number,jt_dt date ,
jt_emp_code varchar2(6),jt_duration number );


insert into ot_job_det values ( 'time',1,'04-dec-2012','AA',10);

insert into ot_job_det values ('time',2,'01-dec-2012','AA',12);

insert into ot_job_det values ('time',3,'09-dec-2012','BB',7);

insert into ot_job_det values ('time',4,'14-dec-2012','CC',2.43);

insert into ot_job_det values ('time',5,'15-dec-2012','DD',2);



CREATE TABLE P_CALENDAR_DATE (CAL_CODE VARCHAR2(6),CAL_DT DATE, CAL_WH VARCHAR2(1))

INSERT INTO P_CALENDAR_DATE ( CAL_CODE, CAL_DT, CAL_WH ) VALUES ( 
'WO1',  TO_Date( '12/07/2012 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'W'); 
INSERT INTO P_CALENDAR_DATE ( CAL_CODE, CAL_DT, CAL_WH ) VALUES ( 
'WO1',  TO_Date( '12/14/2012 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'W'); 
INSERT INTO P_CALENDAR_DATE ( CAL_CODE, CAL_DT, CAL_WH ) VALUES ( 
'WO1',  TO_Date( '12/21/2012 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'W'); 
INSERT INTO P_CALENDAR_DATE ( CAL_CODE, CAL_DT, CAL_WH ) VALUES ( 
'WO1',  TO_Date( '12/28/2012 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'W'); 

COMMIT;




SELECT   jt_emp_code, absent_days,yyyymm YR_MTH --TO_CHAR (jeh_dt, 'YYYYMM') YR_MTH
    FROM (SELECT   jt_emp_code, TO_CHAR (jt_dt, 'YYYYMM') yyyymm,
                   GREATEST
                      (  30
                       - (SELECT COUNT (cal_dt)
                            FROM p_calendar_date
                           WHERE TO_CHAR (cal_dt, 'YYYYMM') = TO_CHAR (jt_dt, 'YYYYMM'))
                       - CASE
                            WHEN COUNT (*) =
                                   TO_NUMBER (TO_CHAR (LAST_DAY (jt_dt),
                                                       'dd')
                                             )
                               THEN 30
                            ELSE COUNT (*)
                         END,
                       0
                      ) absent_days
              FROM ot_job_det
             WHERE to_char(jt_dt,'YYYYMM')='201212'             
          GROUP BY TO_CHAR (jt_dt, 'YYYYMM'), jt_emp_code, LAST_DAY (jt_dt)) a
   WHERE absent_days > 0
ORDER BY jt_emp_code

Previous Topic: Converting number column to date column
Next Topic: How to convert a string into numeric other than to_number
Goto Forum:
  


Current Time: Wed Jul 30 04:46:27 CDT 2014

Total time taken to generate the page: 0.05899 seconds