This discussion is archived
0 Replies Latest reply: Nov 23, 2012 3:05 AM by 944789

# chanllenging matrix output

Currently Being Moderated
i have one table where we record the 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
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.
The table and inserts are as below.
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.
i am getting the output as below but i want to display the data in reports 6i in matrix format as below and take it to
excel using rpt2xls tool.
``````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;

--Current output is
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

--If you see the normal hours of jt_no 4 as 2 but it has to be 2.50, the desired output is as follows

1/11/2012  04/11/2012 09/11/2012  14/11/2012 15/11/2012
AA            8         8
4         2
BB                                             0
7

CC                                                                 2.5
0
DD                                                                                   2
0``````

#### Legend

• Correct Answers - 10 points