Home » SQL & PL/SQL » SQL & PL/SQL » Calculating absent days
Calculating absent days [message #329668] Thu, 26 June 2008 03:13 Go to next message
micro_oracle
Messages: 41
Registered: December 2007
Location: UAE
Member

hi all
I have the following issue :

I had created the following table
WHICH CONTAINS ALL DAILY LOGS FOR WORKER (TABLE INVOKED IN DAILY BASIS TO STORE TIME SHEET FOR EACH WORKER)

CREATE TABLE ABSENT_COUNT 
( EMP_ID  VARCHAR2(5),
WRK_DAY   DATE,
ABSENT_FLG  VARCHAR2(1), -- Y/N
OT_HOURS    NUMBER(4,2));


INSERT INTO ABSENT_COUNT VALUES ('10101','06/25/2008','N',02.0);
INSERT INTO ABSENT_COUNT VALUES ('10102','06/26/2008','N',02.0);
INSERT INTO ABSENT_COUNT VALUES ('10103','06/27/2008','Y',02.0);
INSERT INTO ABSENT_COUNT VALUES ('10106','06/30/2008','N',02.0);
INSERT INTO ABSENT_COUNT VALUES ('10107','07/01/2008','Y',02.0);
INSERT INTO ABSENT_COUNT VALUES ('10108','07/02/2008','Y',02.0);
INSERT INTO ABSENT_COUNT VALUES ('10109','07/03/2008','N',02.0);
INSERT INTO ABSENT_COUNT VALUES ('10110','07/04/2008','N',02.0);
INSERT INTO ABSENT_COUNT VALUES ('10113','07/07/2008','Y',02.0);
INSERT INTO ABSENT_COUNT VALUES ('10114','07/08/2008','N',02.0);
...
...
...



SELECT * FROM ABSENT_COUNT;

EMP_ID	WRK_DAY	ABSENT_FLG	OT_HOURS
10101	06/25/2008 00:00:00	N	2.5
10101	06/26/2008 00:00:00	N	2
10101	06/27/2008 00:00:00	Y	2
10101	06/30/2008 00:00:00	N	2
10101	07/01/2008 00:00:00	Y	2
10101	07/02/2008 00:00:00	Y	2
10101	07/03/2008 00:00:00	N	2
10101	07/04/2008 00:00:00	N	2
10101	07/07/2008 00:00:00	Y	2
10101	07/08/2008 00:00:00	N	2
10101	07/09/2008 00:00:00	N	2
10101	07/10/2008 00:00:00	N	2
10101	07/11/2008 00:00:00	N	2
...     ...                     ...     ...
...     ...                     ...     ...
...     ...                     ...     ...
...     ...                     ...     ...
...     ...                     ...     ...



NOW I NEED TO REACH TO THE FOLLOWING :
CREATE VIEW TO SHOW SHOW THE FOLLOWING RESULT SET FOR EACH EMPLOYEE:



EMP_ID	WEEK_FROM       WEEK_TO     ABSENT_DAYS  DAYS_DEDUCTED
10101   MON,06/30/2008  07/04/2008  2            1
10101   MON,07/07/2008  07/11/2008  1            0
...     ...             ...         ...          ...
...     ...             ...         ...          ...
...     ...             ...         ...          ...

FOR EACH EMPLOYEE

NOW THE ISSUE IS : i NEED BREAK DOWN THE GIVEN PAYROLL MONTH IN A WEEKS (4,5 OR 6 @ MAXIMUM) AND SINCE ANY EMPLOYEE GOT 2 OR MORE DAYS OF ABSENTEEISM IN ENTIRE THE WEEK THEN i WILL PUT FOR HIM 1 IN DAYS_DEDUCTED COLUMN ELSE IT WILL REMAIN 0

NOW I'M FACING DIFFICULTIES IN MAKING A WEEK LIST,

CAN ANY BODY HELP ME TO PRODUCE :
1- WEEK LIST (WEEK_START_DATE, WEEK_END_DATE, MONTH, YEAR) 
2- MAKE THAT CALCULATION FOR EACH EMPLOYEE

WAITING YOUR ADVISES



Re: Calculating absent days [message #329710 is a reply to message #329668] Thu, 26 June 2008 05:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

DON'T POST IN UPPER CASE.

Regards
Michel
Re: Calculating absent days [message #329717 is a reply to message #329668] Thu, 26 June 2008 05:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This should give you a start: It's a calendar query that will give you the start and end dates of the weeks in the year.
select trunc(dte,'W') week_start
      ,trunc(dte,'W')+6 week_end
      ,to_char(dte,'Month') Mnth
      ,to_char(dte,'YYYY') Yr
from  (select trunc(sysdate,'YYYY')+(7*(level-1)) dte
       from   dual
       connect by level <= ceil((add_months(trunc(sysdate,'YYYY'),12)-trunc(sysdate,'YYYY'))/7));

[Updated on: Thu, 26 June 2008 05:41]

Report message to a moderator

Re: Calculating absent days [message #329731 is a reply to message #329710] Thu, 26 June 2008 06:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Thu, 26 June 2008 12:25

DON'T POST IN UPPER CASE.

Regards
Michel


Don't reply in upper case, please. Razz
Re: Calculating absent days [message #329732 is a reply to message #329668] Thu, 26 June 2008 06:25 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Razz Laughing
Re: Calculating absent days [message #329740 is a reply to message #329731] Thu, 26 June 2008 07:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This was deliberate, I thought someone will answer as you did. Razz

Regards
Michel
Re: Calculating absent days [message #330035 is a reply to message #329717] Fri, 27 June 2008 07:11 Go to previous messageGo to next message
micro_oracle
Messages: 41
Registered: December 2007
Location: UAE
Member

thanks JRowbottom

I used to get from you a quick resolution

Smile
Re: Calculating absent days [message #330039 is a reply to message #330035] Fri, 27 June 2008 07:28 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
micro_oracle wrote on Fri, 27 June 2008 20:11
thanks JRowbottom

I used to get from you a quick resolution

Smile



me too!!! sir JRowBottom has a good way of showing the logic always =)
Re: Calculating absent days [message #330045 is a reply to message #330039] Fri, 27 June 2008 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But searching a little bit would also give you the solution as calendar query has already been answered many times.
Why does Senior have to repeat the same thing over and over?
ehegagoka, micro_oracle can you answer this for me?

Regards
Michel
Re: Calculating absent days [message #330125 is a reply to message #330045] Fri, 27 June 2008 11:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why do you think it's a problem for other people to repeat an answer to people seeking help, while you repeat your pointer to the guidelines at least twenty times a day?
Re: Calculating absent days [message #330136 is a reply to message #330125] Fri, 27 June 2008 11:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good catch!
...but this does not answer the question

Regards
Michel

[Updated on: Fri, 27 June 2008 11:37]

Report message to a moderator

Re: Calculating absent days [message #330190 is a reply to message #330045] Fri, 27 June 2008 18:35 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
to michel:
i'm not sure i understand your question. i didn't ask the "calendar" question, my reply was to acknowledge sir JRowBottom's way of helping the other people without saying to them how stupid they are. as the other posters would say, don't reply if you dont want to answer, or if you get pissed off by the redundancy of the question. i know that you and all the "Senior/Expert" in oracle here just want to imposed that people try first something before they ask questions. maybe sometimes i ask questions without first trying, but maybe it wasn't intentional, it's just that some factors like the time available to go over the docu's you will give as opposed to the time given to us to solved our problems. as for the "senior" part, i think i've been tag as a senior here not because im an expert, but just because the number of times i ask and reply to other people to learn from them and to help them also with my "little" knowledge in oracle. to be honest, i'm not a pure oracle developer, my work had been a mix of perl, java, c, c++, c#, pl/sql, that's why my postings usually have some gaps of a year or so. most of the time i work with these languages at the same time as the applications are written in different languages. i only study what i need to used in my work. so maybe our questions may sound stupid to you and the others, but to me it's not, that's why we go this so-called forum to ask for "help". i don't remember me forcing other members to answer to my questions, and while waiting for the answer or hint, i'm also doing my own research. if you think some of my postings here in this forum are "non-sense" or useless, feel free to delete it, as you are the so-called moderator. i'm just glad that other people like sir JRowBottom answer questions even though the question is not formatted but readable, upper-case but when i read it, it didn't came to me that the OP was angry or shouting, i focus on his problem. i think it's just a matter of one's discretion as to a post is "wrong" or not. sorry for this "novel" i made. i just want to make some points (if i made any) that i think the other members would also like to say.

i think as long as your intention is to "help" other people there's no need to degrade him of his mistakes in making his post or anything. as for me, i'm just here to learn, and use this forum to keep up with my work, as this forum have been a lot of help to me in my "oracle stuff". but still thank you very much for giving your time and also to the other moderators.


as to the OP, please check if this would be of any help, for the week start and week-end.


create or replace type obj_date as object(o_start date, o_end date, o_week number);
create or replace type arr_date as table of obj_date;

create or replace function gen_date(yr varchar default to_char(sysdate, 'RRRR'))
  return arr_date
  pipelined
  as
  p_based date := to_date('01/01/' || yr, 'MM/DD/RRRR');  
  p_o_date obj_date;
   
  s date;
  e date;
  w number;
begin
   for i in 1 .. 52
   loop
       select p_based st, next_day(p_based, 1) en, to_char(p_based, 'IW') week       
       into s, e, w
       from dual;
       
       p_o_date := obj_date(s, e, w);        
       p_based := e + 1;
       pipe row(p_o_date);
   end loop;
   return;
end;


--using SQL
select o_start, o_end, o_week
from table (gen_date)
where o_week = 15;

--using blocks
declare
  s date;
  e date;
  w number;
begin
  select o_start, o_end, o_week
  into s, e, w
  from table (gen_date)
  where o_week = 15;
  dbms_output.put_line(s || ' to '||e);
end;




regards,
rhani

[Updated on: Fri, 27 June 2008 18:52]

Report message to a moderator

Re: Calculating absent days [message #330213 is a reply to message #330190] Fri, 27 June 2008 23:52 Go to previous message
micro_oracle
Messages: 41
Registered: December 2007
Location: UAE
Member

Hi ehegagoka, michel
Thanks ehegagoka for your solution too, and please listen all to me,

I'm really like to learn oracle and improve my self in, thus Always I would like to introduce my queries to "SENIORS" and see how they can solve it to me and to all posters.

In my work (I'm working as BI analyst) I'm facing a lot of complicated queries with a multible joins, unions and recursions but I'm always preferring to face it by myself and read many documents related.
I could remember that before 3 months I encountered a lot of errors and problems in producing the RMAN Backup script, but that time I handle it lonely through 6 documents I read that time because I need to know the matter.

but here I'm keep posting coz I really don't know how to over come those issues of dates and calenders ( I need a course in Oracle dates ), also I know clearly that I shouldn't duplicate the same inquiry in multiple posts and I shouldn't write in CAPS but Unfortunately I forgot the CAPS on,also I discovered that after posting the topic in the first time my connection get Depredated hence I felt that I lost a post then I re-send it again after moments.

that's all about my mistakes Sad Smile
Previous Topic: HOW DO I DO A COUNT ON PT ID
Next Topic: UPDATE statement caused error
Goto Forum:
  


Current Time: Sat Dec 10 15:06:48 CST 2016

Total time taken to generate the page: 0.08009 seconds