6 Replies Latest reply: Nov 25, 2012 11:24 AM by rp0428

# matrix like output required

i have one table where we record th e daily manhours for all the workers, the data will be entered as total duration, out of which i need to extract the normal hours worked and overtime hours seperately, there are certain rules for extraction as , i want the output in pivot format.
a) The Normal hours are only 8 hours per day , if the total duration is for 10 hours then normal hours will be 8 and overtime will be 2 if its on any day other than friday.
b) If the total duration is 12 hours then 8 will still go as normal and 4 will go as overtime hours.
c) we have to consider the entire duration as overtime hours if its on friday
d) Normal hours column will be 0 on friday.
e) if the duration is less than 8 and its not on friday , then it will be considered as normal hours
f) If the duration is comming as 2.43 or 3.30 then it should be rounded off as 2.50 and 3.50.

The table and inserts are as below.
``````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-Nov-2012','AA',10);

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

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

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

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

COMMIT;

SQL> select case
2           when jt_duration > 8 and
3                trim(to_char(det.jt_dt, 'Day')) <> 'Friday' then
4            8
5           when jt_duration > 0 and
6                trim(trim(to_char(det.jt_dt, 'Day'))) = 'Friday' then
7            0
8           when jt_duration < 8 and
9                trim(to_char(det.jt_dt, 'Day')) <> 'Friday' then
10            round(jt_duration)
11         end normal,
12         case
13           when jt_duration > 8 and
14                trim(to_char(det.jt_dt, 'Day')) <> 'Friday' then
15            jt_duration - 8
16           when jt_duration > 0 and
17                trim(trim(to_char(det.jt_dt, 'Day'))) = 'Friday' then
18            jt_duration
19           when jt_duration < 8 and
20                trim(to_char(det.jt_dt, 'Day')) <> 'Friday' then
21            0
22         end ot_hours,
23         to_char(det.jt_dt, 'Day') jt_day,
24         det.*
25    from ot_job_det det;

/

NORMAL  OT_HOURS JT_DAY    JT_TXN           JT_NO JT_DT      JT_EMP JT_DURATION
------ --------- --------- ------------ --------- ---------- ------ -----------
8         2 Sunday    time                 1 04/11/2012 AA              10
8         4 Thursday  time                 2 01/11/2012 AA              12
0         7 Friday    time                 3 09/11/2012 BB               7
2         0 Wednesday time                 4 14/11/2012 CC            2.43
2         0 Thursday  time                 5 15/11/2012 DD               2

i want the output in this format pivot

JT_EMP_CODE HOURSE_INDICATOR   20121101   20121104   20121109   20121114   20121115

AA                   NORMAL                        8               8
AA                     OT                             4                2
BB                  NORMAL                                                           0
BB                     OT                                                                7
CC                  NORMAL                                                                            2.5
CC                    OT                                                                                   0
DD                NORMAL                                                                                                 2
DD                   OT                                                                                                      0
``````
• ###### 1. Re: matrix like output required
http://www.orafaq.com/forum/m/571349/136107/?&reveal=140823
• ###### 2. Re: matrix like output required
Still no solution.
• ###### 3. Re: matrix like output required
Hi Arif75,

http://www.oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1.php

Regards,
Jitendra
• ###### 4. Re: matrix like output required
Arif75 wrote:
Still no solution.
With free advice, you get what you paid for it.

submit your refund request for a full & complete refund to /dev/null
• ###### 5. Re: matrix like output required
thanks jitendra , actually there are various examples over there, i will go thru that.

Edited by: Arif75 on Nov 24, 2012 10:18 PM
• ###### 6. Re: matrix like output required
>
submit your refund request for a full & complete refund to /dev/null
>
Do you have a solution for Windows users?